Search code examples
daxcalculationmeasuregroup

Needed to update an existing DAX measure, to work in a specific way

I have the following table in Power BI

EpiDates (few thousands records)

       Period   ClientID   AdmDate      DischDate       LOS

       1             222   5/23/2022    12/31/2050      396
       2             222   5/23/2022    12/31/2050      396
       3             222   5/23/2022    12/31/2050      396
       2             333   3/01/2022    12/31/2050      479
       3             333   3/01/2022    12/31/2050      479
       0             444   1/23/2023    12/31/2050      151
       1             444   1/23/2023    12/31/2050      151
       2             555   1/23/2023     2/23/2023       30

I need to create DAX measure – [Active Clients], which will calculate the unique numbers of clients - [ClientID], for the clients, where [DischDate] = 12/31/2050

I have the following DAX code:

 CALCULATE (DISTINCTCOUNT (EpiDates'[ClientID] ), 
            EpiDates'[DischDate]= DATE(2050, 12, 31))

But, when I create a Matrix in Power BI, I receive the following:

      Period   # Active Clients
      0        1
      1        2
      2        2
      3        2

My [Active Clients] measure counts [ClientID]s for each [Period] used as grouped column.

While my goal is to create Matrix, where my measure will count [ClientID]s FOR THE HIGHEST [Period] VALUEs ONLY, for each ClientID, as following:

    Period    # Active Clients
    1         1
    3         2

Period 1 is the highest for the ClientID = 444 (1 Active Client counted)

Period 3 is the highest for the ClientIDs 333, 222 (2 Active Clients counted)

Period 2 is the highest for the ClientID = 555, but [DischDate] is not 12/31/2050 (not counted)

Period 0 has no ClientIDs for which this Period is the highest
(no ClientIDs counted)

Please HELP – if it’s possible to update my existing measure to work the way described above

Solution

  • You need to use a variable, MaxPeriod, to store the maximum period value from the EpiDates table. Then, you will filter the data based on this maximum period value, along with the condition for the discharge date being December 31, 2050.

    Active Clients = 
VAR MaxPeriod =
    MAX(EpiDates[Period])  -- Calculate the maximum period
RETURN
    CALCULATE(
        DISTINCTCOUNT(EpiDates[ClientID]),
        EpiDates[DischDate] = DATE(2050, 12, 31),
        EpiDates[Period] = MaxPeriod  -- Filter by the maximum period
    )