Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  DAX formula assistance

    Posted Aug 15, 2022 03:48 PM
    Edited by Larry D Aug 15, 2022 05:34 PM

    Hello, I have a PBI star schema model. I'm trying to define a variable using a field (Health Index) that resides in one of the dimension tables.  The field's value can change month to month. See chart below.   

    Country Date Health Index
    China 3/31/22 High
    China 2/28/22 Average
    China 1/31/22 High

    I want to be able to write a a DAX formula called Health Index-Dynamic to display the data such that it shows the index value corresponding to the maximum date selected in the slicer, so if in the slicer the date selected is 3/31/22, I would like the measure to have a value of 6:

    Country Date Health Index - Dynamijc
    China 3/31/22 High

    If the date values selected in the slicer are for 3/31/22 and 2/28/22, then I want to still see just one row with the value of 'High', since this corresponds to the maximum date selecte:

    Country Date Health Index-Dynamic
    China 3/31/22  High


    And if I select dates that are not truly the maximum, like 2/28/22 and 1/31/22, I still want to see just one row, showing the value corresponding to 2/28/22 since this is the maximum date selected in the prompt

    Country Date Health Index-Current
    China 2/28/22    Average

    and if I select 1/31/22, I want to see one row, showing the value corresponding to 1/31/22, since this is the max value selected from the prompt


    Country Date Health Index-Current
    China 1/31/22    High



    I tried the following DAX formula but it displays all the unique values of the Health Index, even if I select dates in the past where the value was the same. I know the joins are correct,

    Health Index Current = 
    var _a = 
        MAXX(
            ALLSELECTED('Dim Date Table'[Date])
            ,'Dim Date Table'[Date]
        )
    
    return 
       CALCULATE(
            MAX('Dim Health Indexes Table'[Health Index])
            ,'Dim Date Table'[Date] = _a
       )
    

    Thanks for any assistance



    ------------------------------
    Larry D
    ------------------------------


  • 2.  RE: DAX formula assistance

    Posted Aug 16, 2022 03:08 PM
    If your model is set up correctly, then when you make a selection in a slicer, the whole model will be filtered by that selection.  So, for example, when you select the dates 2/28/2022 and 1/31/2022, the whole model will be filtered to include just those values.  As a result, you don't need to use the ALLSELECTED.  You should be able to modify the code to look more like below.  The only reason this might not work would be if your slicer is using a date filed that is on the wrong side of the relationship (look at the direction of the arrows on your relationships in the data model diagram)

    VAR maxDate =
        MAX('Dim Date Table'[Date])
    RETURN
    CALCULATE(
    MAX('Dim Health Indexes Table'[Health Index]),
    'Dim Date Table'[Date] = maxDate
    )


    ------------------------------
    Kaz Shakir
    Sr. Program Manager, Asset Planning
    TN
    ------------------------------