How to use Filter selection in a DAX calculation

[latheesh89]

Dear Techies,

Let's say I'm working in a healthcare domain, where I have Patient table, which tracks the daily activites of the patient during his stay. I want to calculate the no of days he has stayed. Eg:- Let's take Patient X stayed between 2016-01-01 to 2016-06-30 (182 days). I need this count 182 as output and it should logically work even when I use .the Date filter. If I select 2018-01-19 date from date filter, I still should see 182. Also if I select 2016-06-20, it should give me 172 as output. 

Basically I am trying to achieve something like this,  

DaysStayed:= CALCULATE(COUNT(PatientID), FILTER('Patient', [TentureDate] <= "Selection from the filter"))

to put the above expression in SQL statement, it should be something like this SELECT COUNT(PatientID) FROM Patient WHERE PatientID=10 AND TentureDate <= @DateFilterselection --  By this whatever date you feed, you would get the appropriate result

Whenever I add Date filter and do any selection, only Null value is being returned. Any suggestion for the above problem or any alternate function or idea to achiever?

Thanks,

Latheesh

[ccakjcrx]

Hey @latheesh89!

As with anything with DAX, there are many ways to arrive at the outcome.

I don't have access to your .pbix file, so I created one to test with; you can access that HERE. I have two tables: Patient & PatientActivity. With my patients, I have patient names, and of course some patient id (an integer). Having an integer will help when using COUNTROWS with FILTER if filtering on patient identifiers. 

The calculate function will prove helpful when counting and taking into consideration filters (e.g., slicers, sliders, etc.). After CALCULATE's first parameter, you can tell CALCULATE to ignore the filter context by utilizing ALL(PatientActivity[Date]), consider the filter context by utilizing VALUES(PatientActivity[Date]), or consider the filter context by not putting anything in the second parameter. Of course, in my test data, I simply have a slicer tied to the PatientActivity[Date] column.

Here is a screenshot of my table visual:

Here are my measures: 

MsrCOUNTROWSFILTER = 
COUNTROWS(
    FILTER(PatientActivity,
        COUNTROWS(
            FILTER(RELATEDTABLE(Patient),Patient[PatientId])
        )
    )
)

MsrCALFILTER = 
CALCULATE(
    COUNTROWS(PatientActivity),
    VALUES(PatientActivity[Date])
)

MsrCALALL = 
CALCULATE(
    COUNTROWS(PatientActivity),
    ALL(PatientActivity[Date])
)

CALCULATE is REALLY smart, you just have to tell it either what to ignore or to consider.

Hope this helps.

[latheesh89]

Thanks 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.

[ccakjcrx]

Hey @latheesh89!

I'm speculating here, but it sounds like the filter/slicer may be affecting the 'earliest' date. Given the adjustment to the filter context is yielding zero (i.e., no output), it sounds like the filter/slicer's has a beginning date that falls after the patient's discharge date. If that is true, then the filter context can produce 'nothing' in your example IF your expression isn't ignoring the filter context (i.e., the slicer). 

That doesn't mean there is anything wrong with your data model; however, it means the expression calculating the 'patient stay' days ISN'T ignoring the filter context. In that case, the expression would need to be modified to ignore the filter context. Once that happens, you should get the correct 'patient stay' days (i.e., patient activity)--irrespective of filters/slicers. 

I'm not sure you want to make your data available as it may contain PHI. If needed, you can msg me; I'll give you my phone number and I might be able to help out. 

[latheesh89 · Accepted solution]

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.

[nickchobotar]

@latheesh89

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

Please paste your measure here as you wrote it in your model

N -

[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]

@latheesh89

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-