Compare an observed value to a pivoted rank table, returning both values > and <= Observed?

[djallarii]

We have a rank table that's stored like this:

Measure Name	Rank_5	Rank_10	Rank_15	Rank_20	Rank_25	Rank_30	Rank_35	Rank_40	Rank_45	etc
Measure A	1.242	6.242	11.242	16.242	21.242	26.242	31.242	36.242	41.242	etc

I already have a solution where I unpivot the rank table, add an index starting at 0, another index at 1, and merge the queries to provide the range in a row. 

This works fine but I was curious, for education purposes, if there is a DAX solution that would result in these Rank values suggesting an observed value of 17.85?

Rank Low = 16.242

Rank High = 21.242

[djallarii]

In this case, I would have to write one DAX expression for each column pair to evaluate the ranges, correct?

What if the Rank table aren't consistently 20 columns of ranks in increments of 5?

Give my original scenario I would do this with looping logic in our current system, using an int counter instead of having to rewrite the logic. The loop logic would basically be this but with less actual lines:

Observed Value = 17.85

Start Column N = 3

Observed Value > Column N and <= Column N+1 = False
Observed Value > Column N + 1 and <= Column N + 2 = False

Observed Value > Column N + 2 and <= Column N + 3 = False

Observed Value > Column N + 3 and <= Column N + 4 = False

Observed Value > Column N + 4 and <= Column N + 5 = True

Observed Value = 17.85

Column N + 4 = Rank_20

Column N + 5 = Rank_25

Rank Low Value = 16.242

Rank High Value =  21.242

Does DAX have a column scannig type lookup without static column references?

[123abc]

I understand your requirement better now. If the number of columns and their increments are not consistent, and you want a more dynamic solution without having to explicitly reference each column, you can use a combination of UNION and SELECTCOLUMNS functions to create a virtual table that represents the Rank table in a more dynamic way. Then, you can iterate over this virtual table to find the corresponding range for the observed value.

Here is an example:

ObservedValue = 17.85
StartColumnIndex = 3

RankTable =
DATATABLE (
"Measure Name", STRING,
"Rank_5", FLOAT,
"Rank_10", FLOAT,
"Rank_15", FLOAT,
"Rank_20", FLOAT,
"Rank_25", FLOAT,
"Rank_30", FLOAT,
"Rank_35", FLOAT,
"Rank_40", FLOAT,
"Rank_45", FLOAT,
-- Add more columns as needed
{
{"Measure A", 1.242, 6.242, 11.242, 16.242, 21.242, 26.242, 31.242, 36.242, 41.242}
-- Add more rows as needed
}
)

VirtualTable =
UNION (
SELECTCOLUMNS ( RankTable, "Measure Name", RankTable[Measure Name], "Rank", RankTable[Rank_5] ),
SELECTCOLUMNS ( RankTable, "Measure Name", RankTable[Measure Name], "Rank", RankTable[Rank_10] ),
SELECTCOLUMNS ( RankTable, "Measure Name", RankTable[Measure Name], "Rank", RankTable[Rank_15] ),
SELECTCOLUMNS ( RankTable, "Measure Name", RankTable[Measure Name], "Rank", RankTable[Rank_20] ),
SELECTCOLUMNS ( RankTable, "Measure Name", RankTable[Measure Name], "Rank", RankTable[Rank_25] ),
-- Add more columns as needed
SELECTCOLUMNS ( RankTable, "Measure Name", RankTable[Measure Name], "Rank", RankTable[Rank_30] ),
SELECTCOLUMNS ( RankTable, "Measure Name", RankTable[Measure Name], "Rank", RankTable[Rank_35] ),
SELECTCOLUMNS ( RankTable, "Measure Name", RankTable[Measure Name], "Rank", RankTable[Rank_40] ),
SELECTCOLUMNS ( RankTable, "Measure Name", RankTable[Measure Name], "Rank", RankTable[Rank_45] )
-- Add more columns as needed
)

ObservedRange =
MINX (
FILTER (
VirtualTable,
[Rank] <= [ObservedValue]
),
[Rank]
)

RankLow =
CALCULATE (
MINX (
FILTER (
VirtualTable,
[Rank] = ObservedRange
),
[Rank]
)
)

RankHigh =
CALCULATE (
MAXX (
FILTER (
VirtualTable,
[Rank] = ObservedRange
),
[Rank]
)
)

This approach creates a dynamic virtual table (VirtualTable) by combining the columns from RankTable. Then, it finds the observed range and calculates the Rank Low and Rank High values based on that range. This way, you don't need to explicitly reference each column in your DAX expressions.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[123abc]

Certainly! In Power BI or any other tool that supports DAX (Data Analysis Expressions), you can achieve this using DAX measures. Given your rank table structure, you can create a measure that compares an observed value to the Rank values and returns the appropriate range.

Let's say your observed value is in a table named 'ObservationTable', and the observed value is in a column named 'ObservedValue'. Here's a DAX measure that would give you the desired result:

ObservationRange =
VAR ObservedValue = SELECTEDVALUE('ObservationTable'[ObservedValue])
RETURN
CALCULATE(
MAX('RankTable'[Measure Name]),
'RankTable'[Rank_5] <= ObservedValue
)
& " - " &
CALCULATE(
MIN('RankTable'[Measure Name]),
'RankTable'[Rank_5] <= ObservedValue
)

This measure calculates the range based on 'Rank_5'. You can adapt this for other rank columns by replacing 'Rank_5' with the appropriate column name.

You can then use this measure in your reports or visualizations to display the observed range based on the rank values. If the rank values are not in the same table as the observed values, you may need to adjust the relationships in your model or use related functions like RELATED or RELATEDTABLE in your DAX measure.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.