Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Shaker
Frequent Visitor

Create DAX measure using output from another DAX measure to determine an overall status

I have created a DAX measure called 'Risk Ratio' that outputs a risk ratio for each school and school year.  My challenge is to create a new DAX measure using the multi-year Risk Ratio measure to determine an overall status.  I have provided a sample report that has the data and Risk Ratio measure if someone wants to try building the new measure to produce an overall status for each school.  Any help would be greatly appreciated. 

 

Example Report 

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @Shaker ,

 

Please try:

First turn on the column subtotal and change its name to "Status":

vjianbolimsft_0-1685587716617.png

Then apply the measure to the matrix visual:

Measure =
VAR _a =
    SUMMARIZE ( 'RDS DimLeas', 'RDS DimLeas'[LEA Name] )
VAR _b =
    DISTINCT ( 'Year'[Year] )
VAR _c =
    ADDCOLUMNS ( CROSSJOIN ( _a, _b ), "Risk Ratio", [Risk Ratio] )
VAR _d =
    SELECTEDVALUE ( 'State Set Threshold'[State Threshold] )
VAR _e =
    COUNTROWS ( FILTER ( _c, [Risk Ratio] >= _d ) )
VAR _f =
    ADDCOLUMNS (
        _c,
        "Flag",
            IF (
                RANKX ( _c, VALUE ( MID ( [Year], FIND ( "(", [Year] ) + 1, 4 ) ),, ASC, DENSE )
                    = RANKX ( _c, [Risk Ratio],, DESC, DENSE ),
                1,
                0
            )
    )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Year'[Year] ), [Risk Ratio],
        NOT ( ISINSCOPE ( 'Year'[Year] ) )
            && _e = 0
            && SUMX ( _c, [Risk Ratio] ) <> BLANK (), "Not sig dispro at all",
        NOT ( ISINSCOPE ( 'Year'[Year] ) )
            && _e = 1, "At-risk year 1",
        NOT ( ISINSCOPE ( 'Year'[Year] ) )
            && _e = 2, "At-risk year 2",
        NOT ( ISINSCOPE ( 'Year'[Year] ) )
            && _e = 3
            && SUMX ( _f, [Flag] ) = 3, "Reasonable progress",
        NOT ( ISINSCOPE ( 'Year'[Year] ) )
            && _e = 3
            && SUMX ( _f, [Flag] ) <> 3, "Significantly disproportionate"
    )

 Final output:

vjianbolimsft_1-1685587752765.png

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-jianboli-msft
Community Support
Community Support

Hi @Shaker ,

 

Please try:

First turn on the column subtotal and change its name to "Status":

vjianbolimsft_0-1685587716617.png

Then apply the measure to the matrix visual:

Measure =
VAR _a =
    SUMMARIZE ( 'RDS DimLeas', 'RDS DimLeas'[LEA Name] )
VAR _b =
    DISTINCT ( 'Year'[Year] )
VAR _c =
    ADDCOLUMNS ( CROSSJOIN ( _a, _b ), "Risk Ratio", [Risk Ratio] )
VAR _d =
    SELECTEDVALUE ( 'State Set Threshold'[State Threshold] )
VAR _e =
    COUNTROWS ( FILTER ( _c, [Risk Ratio] >= _d ) )
VAR _f =
    ADDCOLUMNS (
        _c,
        "Flag",
            IF (
                RANKX ( _c, VALUE ( MID ( [Year], FIND ( "(", [Year] ) + 1, 4 ) ),, ASC, DENSE )
                    = RANKX ( _c, [Risk Ratio],, DESC, DENSE ),
                1,
                0
            )
    )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Year'[Year] ), [Risk Ratio],
        NOT ( ISINSCOPE ( 'Year'[Year] ) )
            && _e = 0
            && SUMX ( _c, [Risk Ratio] ) <> BLANK (), "Not sig dispro at all",
        NOT ( ISINSCOPE ( 'Year'[Year] ) )
            && _e = 1, "At-risk year 1",
        NOT ( ISINSCOPE ( 'Year'[Year] ) )
            && _e = 2, "At-risk year 2",
        NOT ( ISINSCOPE ( 'Year'[Year] ) )
            && _e = 3
            && SUMX ( _f, [Flag] ) = 3, "Reasonable progress",
        NOT ( ISINSCOPE ( 'Year'[Year] ) )
            && _e = 3
            && SUMX ( _f, [Flag] ) <> 3, "Significantly disproportionate"
    )

 Final output:

vjianbolimsft_1-1685587752765.png

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This is incredibly helpful, thank you Jianbo!  Is there a way to make charts to show the Status similar to the one below?  I need to create visuals using the count of schools (LEAs) by Status.

 

Sample created in Excel...

Screenshot 2023-06-01 093510.jpg

Hi @Shaker ,

 

I'm sorry this question is beyond the original topic of this thread, in order to make the thread more relevant, please consider marking the reply that helped you and creating a new thread for the new question, so that more users can participate and better help others with similar questions.

 

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Understand, I'll send a new request. Thanks for your help! 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors