DAX Lookup by Limit

[srehman]

Hi Team,

I need to achieved result by DAX.

Class table & Product table lookup (if percentage less than or equal to 15% assign class "AA" and so on.
I have watched some videos about frequency category. But here everything in percentages.

Product percentages achieved from Qty could be any , i can provide it if you need any info.

Please if this data not help to achieved results or not according forum please disregard this post.

Thanks

Book1
	A	B
2	Class Table
3	Class	Calss Percentages
4	AA	15%
5	A	30%
6	B	50%
7	C	75%
8	D	100%
Sheet1

Book1
	E	F	G
2	Prouduct Table		Need to achieved by DAX
3	Prouduct	Percentages	LESS THAN OR EQUAL TO CLASS Percenatge
4	A1	12%	AA
5	A1	40%	B
6	A1	85%	C
7	B1	5%	AA
8	B1	75%	C
9	B2	29%	A
10	C1	0.5%	AA
11	C2	62%	C
12	C3	4%	AA
Sheet1

 

[srehman]

Good Morning JustynaMK,
Thanks for your response i spent lot of time thinks in many ways with limited knowledge. I was watching video about IF condition statements and found some amazing stuff about switch () TRUE() by using it i can achieved result in very simple way.
So i only need to work on Prod table i have used SWITCH() to conditionally assign class on Percentages column, which provide a calculated column

Could be following approach not good at professional level. As i am beginner trying and spending lot of time to learn.



Class =
switch(
TRUE(),
ProdTab[Percentages] <= 0.15, "AA",
ProdTab[Percentages] <= 0.30, "A" ,
ProdTab[Percentages] <= 0.50,"B",
ProdTab[Percentages] <= 0.75,"C",
ProdTab[Percentages] <= 1,"D",
"None"
)

Thanks a lot you provide me a hope to learn in different way.

 

[JustynaMK]

Oh it is a very good approach indeed I'm a big fan of SWITCH function myself.

The only negative about it is that all conditions (i.e. bands) are specified within a function. So if you wanted the user to be able to easily modify the bands (e.g. from 15% to 20% for "AA") or if your source was changing frequently, the previous solution that we have developed earlier in this thread would be more flexible as it is linked directly to the source (i.e. Excel file). However, in the case of SWITCH function, you (administrator/developer) would need to manually adjust the SWITCH parameters every time such need arises.

Both solutions are very good, it all depends on the environment you work in and what are the business needs.

 

[srehman]

You are absolutely right i like first solution as well as you solved,
I have same thought what if percentage values will change with different scenarios.
I will definitely find out something when i learn more.
At this time for me very difficult to find out how can i change formulas according to the conditions and which one is correct choice.
I am trying.
Once again thanks for your support and time.

 

[JustynaMK]

Don't worry, we're all learning! Enjoy the process
Take care & good luck with your report