Hello Community,
I am still very new to Power BI and especially DAX coding. I am trying my best to describe my problem:
I have two tables,
Active[ID]: numeric, one ID per doctor
Active[DateFrom]: start date
Active[DateTo]: end date or blank if ID is still valid
this table includes all active doctors that do exist in a specific area
and then I have another table which includes only those doctors who offer an appointment at a date in this specific area:
Appointment[ID]: numeric, one ID per docotor
Appointment[Date]: date
I want to set the number of doctors that offer appointments in relation to the number of all existing doctors and I want to do this by filtering a date.
For Appointment[ID] I can simply use DISTINCTCOUNT and a filter slicer, no problem.
But the filter slicer is not so easily working for the Acitve[ID] as I have a date from and date to there.
So I need DISTINCTCOUNT(Active[ID]) to change when selecting dates via my filter slicer of Appointment[Date].
With the help of google and the following measure I made it so far that this distinctcount changes when I select one date and when no date (so all dates) are selected:
PickThis =
var _selectedDate = SELECTEDVALUE(Appointment[Date])
return
IF(
ISBLANK(_selectedDate), CALCULATE(DISTINCTCOUNT(Active[ID])),
CALCULATE(DISTINCTCOUNT(Active[ID]), ALL(Active[DateFrom],Active[DateFrom]), Active[DateFrom] <= _selectedDate && (Active[DateTo] >= _selectedDate || ISBLANK(Active[DateTo] )))
)
Still missing is the case of multiple selections. At the moment I get the same numbers as if no data is seleced. Can anybody help?
