get the dynamic ranges based on the table in SSAS tabular model

[Anonymous]

Hi Team,

Need help in SSAS tabular model

I am having one product table with the below details: 

I have created one custom table in SSAS

DEFINE TABLE Ranges= DATATABLE (
"Ranges", STRING,

"Min Range", INTEGER,
"Max Range", INTEGER,
{
{"0-10", 0, 10 },
{ "10-30",10, 30 },
{ "30-60",30, 60 },
{ ">60",60, 100 }
}
)
EVALUATE Ranges

I need to get the range from range table based on the below condition

i

product[sales]>minrange && sales <max range then range

final output table:

Thanks In Advance

[selimovd · Accepted solution]

Hey @Anonymous ,

the following calculated column should do that:

Range =
VAR vRowSales = 'product table'[sales]
RETURN
    CALCULATE(
        MAX( Ranges[Ranges] ),
        Ranges[Min Range] < vRowSales && vRowSales <= Ranges[Max Range]
    )

Be aware that you are not clear with your definition of the beginning/endings of the ranges. As a user I would expect the sales of 10 to appear in both ranges (0-10 and 10-30) as both include 10.

Maybe you should do the ranges like 0-10, 11-30, 31-60, >60

Like this it would be clear in which range which number belongs to.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic