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 ccakjcrx !
As I am still getting familiar with DAX, your approach really helped me to understand more. I guess you are the right person to help me out. I am looking for a different solution though. I should have given the sample table data, here it goes
| PatientID | Date | Activity |
| 100 | 01-01-2016 | A1 |
| 100 | 02-01-2016 | A2 |
| 100 | 03-01-2016 | A2 |
| 100 | 04-01-2016 | A3 |
| . | . | . |
| . | . | . |
| . | . | . |
| 100 | 06-30-2016 | A3 |
The above is a sample table & data, where PatientID 100 has 182 record (01-01-2016 to 06-30-2016) and this date is connected to a Date Dimension.
Now coming back to my question, I would need to count the patient's tenture or count the no of record for the particular patient. In our case it is 182. This is easily achievable by COUNT([PatientID]). But the real challenge is when we drag Date dimension to the filter/slicer section and select any future date ( > patient's last stay date 06-30-2016). The count of that patient should still reflect the actual cont 182. If we select any past date say 06-20-2016 it should give count as 172. Since the date dimension is connected to Patient table, while selecting any future date say 01-01-2018 is not yielding any output.
I guess now I am bit more clear about my requirement. Please suggest.
Skilled Sharer
Try this measure. What I am saying here is give me a distinct count of days out of the list of days between the start date and date selected in the slicer - LASTDATE(DimDate[Date])). CALCULATE() lets filter context flow in for your row headers which are Patients.
*** Date slicer is coming from DimDate which is your date dimension
Duration (Dynamic) =
CALCULATE(
DISTINCTCOUNT(Table2[Duration]),
FILTER(
Table2,
Table2[Duration] = LASTDATE(DimDate[Date]))
)
Thanks, Nick
- latheesh89
Thanks 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.
- nickchobotar
- 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,