Forum Discussion

latheesh89's avatar
latheesh89
Icon for Helper II rankHelper II
8 years ago
Solved

How to use Filter selection in a DAX calculation

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

 

 

Need Help
  • latheesh89's avatar
    latheesh89
    8 years ago

    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.

9 Replies

  • ccakjcrx's avatar
    ccakjcrx
    Icon for Resolver I rankResolver I
    8 years ago

    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's avatar
      latheesh89
      Icon for Helper II rankHelper II
      8 years ago

      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

       

      PatientIDDateActivity
      10001-01-2016A1
      10002-01-2016A2
      10003-01-2016A2
      10004-01-2016A3
      ...
      ...
      ...
      10006-30-2016A3

       

      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.

      • nickchobotar's avatar
        nickchobotar
        Icon for Skilled Sharer rankSkilled Sharer
        8 years ago

        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