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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

Hi Team,

Need help in SSAS tabular model

I am having one product table with the below details: 

 

main table.PNG

 

 

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

Rang.PNG

 

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:

Final.PNG

 

Thanks In Advance

1 ACCEPTED SOLUTION
selimovd
Super User
Super User

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
 

View solution in original post

1 REPLY 1
selimovd
Super User
Super User

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
 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.