Share via

Compare values from Ranges Table in SSAS Tabular Model DAX

Sahoo 46 Reputation points
2021-08-23T06:29:57.037+00:00

Hi Team,

I am having one table with below details
125476-trans.jpg

and i am defining one table in ssas for Ranges

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

125511-image.png

Final output:

125409-image.png

Ranges should compare with min and max range from ranges tables . These two table doesn't have relationships.

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,263 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alexei Stoyanovsky 3,416 Reputation points
    2021-08-23T07:47:44.667+00:00

    Apparently you want a calculated column in your one table. Here's one way of coding it: 

    Range = 
var pct = 'one table'[Sales Percentage]
return CALCULATE(SELECTEDVALUE('Ranges'[Ranges], "no single match"),'Ranges'[Min Range] < pct, 'Ranges'[Max Range] >= pct)

    As a side note you might want to tweak the labels for your ranges so it's apparent which boundaries are inclusive and which are exclusive.

    0 comments