I am currently in the middle of creating some custom tooling and surfacing the outcomes in PowerBI. I created a DAX measure to provide some insights as to whether a trip was currently in-progress or had concluded.
Writing the measure was fairly straightforward using a combination of a custom calculated column based on cross-referencing the current date (today) with the date of trip:
- Date Of Visit < Today = Concluded
- Date Of Visit = Today = In-Progress
- Date Of Visit > Today = Scheduled
However, a simple DAX measure such as:
Scheduled Visits = calculate(countrows(SOURCE),SOURCE[Trip Status]=”Scheduled”) will result in:
There is a super simple resolution to this:
Scheduled Visits = IF(calculate(countrows(SOURCE),SOURCE[Trip Status]=”Scheduled”)=BLANK(),0,calculate(countrows(SOURCE),Source[Trip Status]=”Scheduled”))
As you can see, using a simple IF statement quickly resolves the issue:
I am always open to hearing simpler approaches, so if you have one, please let me know!