sqlpowerbidaxadvanced-filter

Applying advanced filter in Power BI DAX, from a different table


I have the following tables:

Episodes: enter image description here

Clients: enter image description here

My DAX calculation sums up [Days_epi] unique values, from Episodes tbl, grouping them by [ProgramID_epi], [EpisodeID_epi], [ClientID_epi].

So, the SUM of [Days_epi] = 3 + 5 + 31 + 8 + 15 + 20 + 10 = 92

Here is my working code for this:

     DaysSUM = 
       CALCULATE (
             SUMX (
               SUMMARIZE (
                  'Episodes',
                  'Episodes'[EpisodeID_epi],
                  'Episodes'[ProgramID_epi],
                  'Episodes'[ClientID_epi],
                  'Episodes'[Days_epi]
                  ),
                  'Episodes'[Days_epi]
             ),
             FILTER (
                 'Episodes',
                 'Episodes'[Category_epi] = "Homeless"
           )
        )

I need to add two advanced filters to the calculation above:

Filter 1 should ONLY KEEP records in Episodes, if the records in the Clients have the difference between [DischDate_clnt] and [AdmDate_clnt] >= 365.

Filter 1 in SQL statement is

    DATEDIFF(DAY, [AdmDate_clnt], [DischDate_clnt]) >= 365)

After that, Filter 2 should ONLY KEEP records in Episodes, if the records in the Clients have [Date_clnt] >= [AdmDate_clnt] + 12 months. (12 month after the Admission Date)

Filter 2 in SQL statement is

      [Date_clnt] <= DATEADD(MONTH, 12, [[AdmDate_clnt])

So, after applying those two filters I expect the records 6 and 10 of the Episodes tbl must be excluded (filtered out), because the records 2 and 3 of the Clients tbl (highlighted in green) are not satisfied my Filter 1 / Filter 2.

Here is the final Episodes dataset I should have (without the 2 records in red): enter image description here

I was starting to update my DAX code as the following (below). But keep receiving error "Parameter is not the correct type"

enter 
 DaysSUM_Filters = 
       CALCULATE (
             SUMX (
               SUMMARIZE (
                  'Episodes',
                  'Episodes'[EpisodeID_epi],
                  'Episodes'[ProgramID_epi],
                  'Episodes'[ClientID_epi],
                  'Episodes'[Days_epi]
                  ),
                  'Episodes'[Days_epi]
             ),
             FILTER (
                 'Episodes',
                 'Episodes'[Category_epi] = "Homeless"
           ), TREATAS(DATEDIFF('Clients'[AdmDate_clnt], 
                               'Clients'[DischDate_clnt], DAY)>=365,
                               'Clients'[Date_clnt])
          )

Not exactly sure how to set those 2 filters correctly in DAX Power BI, as I am relatively new to it.

Please help!


Solution

  • I can't say about all the case. But what is obvious is that you use TREATAS in a wrong way. It works like this TREATAS({"Red", "White", "Blue"}, 'Product'[Color]).

    In your case

    DATEDIFF('Clients'[AdmDate_clnt], 
                               'Clients'[DischDate_clnt], DAY)>=365
    

    will return TRUE or FALSE value. The first argument of TREATAS - is a column or set of columns not a single value.

    You can use the filter like this:

    FILTER(
        'Clients'
        ,DATEDIFF(
            'Clients'[AdmDate_clnt]
            ,'Clients'[DischDate_clnt]
           ,DAY
        )>=365
    )
    

    This will return you a filtered table. This may work if your tables are linked.