Evaluate rows in DAX measure to determine a single status

[Anonymous]

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:

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:

Made reasonable progress:

Any help is appreciated!

[barritown]

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:

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[Anonymous]

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).  

Thanks again!

[barritown]

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]

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)

Thanks!

[barritown]

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]

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.

Thanks!

[barritown]

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]

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.

[barritown]

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]

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

Risk Ratio Report Example PBIX

[barritown]

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

[barritown · 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.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[Anonymous]

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