Forum Discussion
Helper IIHow to use Filter selection in a DAX calculation
Thanks nickchobotar & ccakjcrx
I would like to appreciate both your effort to help me. I indeed learnt lot from you guys.
I was finally able to figure out the calculation,
CALCULATE(COUNT(PatientID), FILTER(ALL('DimDate'), [Date] <= MAX('DimDate'[Date])))
The above calculation resolved both my cases :)
Thanks again for your help and lots of suggestions.
Helper IIThanks Nic,
I tried your formula. But I'm getting count as 752. I am supposed to get 182 since the Patient (100) as per our sample has stayed for 182 days. I guess it is counting till date. Please suggest.
Skilled Sharer- latheesh89
PatientStay:= CALCULATE(COUNT(Patient[PatientID])) --- This gives me the count/ no of day the Patient has stayed.
Eg:- If I filter by any specific Patient say "100" who has stayed from 01-01-2016 till 06-30-2016. The result of the above measure would be 182 which is perfectly correct.
But If I introduce Date dimension in slicer. I get into trouble
Case 1:
Suppose I select any future date say "01-01-2018" in the slicer, I am getting Null since the selected date is out of the range for Patient 100. Although it is logically right, I somehow want to show that 182 count irrespective of my date selection.
Case 2:
Suppose if I select any date that is within the range say "06-20-2016" then I should get the count as 172.
Now, I should re-write the above given calculation to satisfy both case 1 & 2.
Hope this helps. Please suggest.
Thanks,
- nickchobotar
We understand what are you trying to achieve here and it appears the issue is in the data model. Please share a model sample.
Nick-