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

Anonymous — Mon, 23 Aug 2021 07:08:30 GMT

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

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

final output table:

Thanks In Advance

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

selimovd — Mon, 23 Aug 2021 09:23:36 GMT

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

topic get the dynamic ranges based on the table in SSAS tabular model in DAX Commands and Tips

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

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