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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Evaluate rows in DAX measure to determine a single status

Hi all

I have a DAX measure that contains a risk ratio for a given school district and set of years.  The risk ratio works great when displaying in a visual across each school district and year. 

 

Current visual containing the Risk Ratio measure by school district and year:

 

Shaker_3-1682628953775.png

 

The next step (my problem) is to look at Year 1(2021), Year 2(2022), and Year 3(2023) to assign a single status for each school district.

 

How can I evalute the Risk Ratio measure across years to determine if a school district has made reasonable progress in reducing  their risk ratio?  The Risk Ratio measure is complex because it's built on top of other DAX measures.

 

I need the DAX to look at the Risk Ratio measure and determine if the school district has made 'reasonable progress' or not.

Examples: 

Not made reasonable progress:

Shaker_1-1682628285677.png

Made reasonable progress:

Shaker_2-1682628335538.png

 

Any help is appreciated!

 

 

1 ACCEPTED SOLUTION

Hi,

Please check this solution - https://www.dropbox.com/s/j9e5dxlpyke4it9/Risk%20Ratio%20Example.pbix?dl=0 - and see if it works with the real data.

 

The approach is different:

- we rank the table within each school by the ratio 

[At-Risk Enrolled] / [Total Enrolled];
- if everything okay, the year with ID = 1 will have the Rank = 1, with ID = 2 - Rank = 2 and so on;
- we set Flag = 1 if there is a difference;
- in the measure we calculate the sum, if it is zero - OK, otherwise - not OK.

View solution in original post

13 REPLIES 13
barritown
Super User
Super User

Hi @Anonymous, 

Probably I overcomplicate things a little and there is a more elegant solution in place, but I'd solve your problem like this:

- add a calculated column to your table:

Flag = 
VAR _CurYear = [Year]
VAR _CurName = [LEA Name]
VAR _RiskRatioLY = LOOKUPVALUE ( data[Risk Ratio],
                                 data[LEA Name], _CurName,
                                 data[Year], _CurYear - 1 )
VAR Result = IF ( ISBLANK (_RiskRatioLY) || _RiskRatioLY > [Risk Ratio], 0, 1 )
RETURN Result

- add a measure:

Status = IF ( SUM ( data[Flag] ) = 0, "OK", "not OK" )

- add a table visual to show the result:

 

barritown_0-1683031998262.png

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Anonymous
Not applicable

Hi Alexander

I'm still stuck on this issue so thanks for offering a solution.  I'm getting an error because the first argument in LOOKUPVALUE requires a table column.  Risk Ratio is a DAX measure (not a table column).  

 

Shaker_0-1683033055952.png

 

Thanks again!

Unfortunately, not knowing your data model, I can only say that you should have some CALCULATE with your measure instead of my LOOKUPVALUE. If you can prepare a PBIX file with some toy data, upload it somewhere and share a link here, I will give another look.

 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Anonymous
Not applicable

 

Here is a simplified version of the issue.  The challenge is looking at the Risk Ratio measure to determine an overall status of a school.  Using a measure is better than a calculated column in my scenerio. 

 

When a school risk ratio is trending down the status is 'Reasonable progress', else 'Did not make reasonable progress'.

 

Risk Ratio Example Report (PBIX)

 

Shaker_1-1683043884829.png

Thanks!

Are you going to replace "Year 1", "Year 2" and "Year 3" with some numeric values? Or do they need to be kept like that?

 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Anonymous
Not applicable

Hi - Year1, Year2, and Year3 does not need replaced.  A new measure (in blue below) is needed that evaluates the Risk Ratio Measure across multiple school years to determine a single overall Progress Status per school.

 

Each school receives a single Progress Status based in the multi-years Risk Ratio Measure.

 

Shaker_0-1683119628307.png

Thanks!

 

Hi,

It is easier to operate with years when they are numeric. Another obstacle then. But okay.

One more question about years: how many of them you have in your dataset? 3 for each school? Or this number is not fixed and varies from school to school?

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

 

Anonymous
Not applicable

Hi

In the example PBIX the Risk Ratio Measure is percentage with 2 decimals but using numeric is fine too. The dataset contains the same number of years for each school. For example, a dataset may have 3 years for each school, 4 years for each school , 5 years for each school, etc... This means the years are fixed from school to school.

My point is: it's easier to get the previous year value in a formula by substracting 1 ( 2023 - 1 = 2022 ) than to perform the same operation with the text value ( "Year 3" - 1 = ? ). That's why it is a bit odd for me that you operate with years in the text form of "Year 1", "Year 2", ..., "Year X".

Anonymous
Not applicable

OK, I understand now.  I do have an index column for year that can be used.

 

Risk Ratio Report Example PBIX

 

Shaker_0-1683124562332.png

 

If you don't want to mess with an additional calculated column, you can compose such a measure:

Progress Status without CC = 
VAR AugmentedTable =
ADDCOLUMNS ( 'Table', 
             "Another Flag",
             VAR CurrentSchool = [School Name]
             RETURN INT ( RANKX ( FILTER ( 'Table', 'Table'[School Name] = CurrentSchool ), [At-Risk Enrolled] / [Total Enrolled], , DESC ) <> INT ( [Year ID] ) ) )
RETURN IF ( SUMX ( AugmentedTable, [Another Flag] ) = 0, "OK", "NOT OK" )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Hi,

Please check this solution - https://www.dropbox.com/s/j9e5dxlpyke4it9/Risk%20Ratio%20Example.pbix?dl=0 - and see if it works with the real data.

 

The approach is different:

- we rank the table within each school by the ratio 

[At-Risk Enrolled] / [Total Enrolled];
- if everything okay, the year with ID = 1 will have the Rank = 1, with ID = 2 - Rank = 2 and so on;
- we set Flag = 1 if there is a difference;
- in the measure we calculate the sum, if it is zero - OK, otherwise - not OK.
Anonymous
Not applicable

 

I decided to use the 'Progress Status without CC' measure to rank and assign a single status per school in the sample report.  

 

Progress Status without CC = 
VAR AugmentedTable =
ADDCOLUMNS ( 'Table', 
             "Another Flag",
             VAR CurrentSchool = [School Name]
             RETURN INT ( RANKX ( FILTER ( 'Table', 'Table'[School Name] = CurrentSchool ), [Risk Ratio Measure], , DESC ) <> INT ( [Year ID] ) ) )
RETURN IF ( SUMX ( AugmentedTable, [Another Flag] ) = 0, "OK", "NOT OK" )

 

Thanks so much for your help!

Shaker

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.