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

 

[JustynaMK]

Hi srehman,

I think this should be achievable via DAX.

Before I proceed with my formula, I need to make sure that my understanding of your scenario is correct - shouldn't row 6 (85%) result in "D" class instead of "C" class? 85% is less than or equal to 100% (class D), not 75% (class C).

 

[srehman]

Absolutely correct sorry typo thanks for your's thoughts.
Waiting for your approach.

 

[JustynaMK]

Great, thanks for confirming!

You can try this method -

Step 1 - add "Class Order" to your Class table (in Power BI, click on a table and go to Table Tools > New column). This will rank your classes in descending order.

Class Order = RANK.EQ(tbl_Class[Class Percentages], tbl_Class[Class Percentages], DESC)

Step 2 - create a measure that will count how many rows in Class table are greater than or equal to a current percentage.

mClass = 
    var vCount =
        COUNTROWS(FILTER(tbl_Class, MAX(tbl_Product[Percentages]) <= tbl_Class[Class Percentages]))
return
    vCount

Do you see where we're going from here? the measure actually also returns the value that matches the Class Order that we want to return. Let's wrap up.

Step 3 - adjust your newly created measure to return the final result -

mClass =
var vCount =
COUNTROWS(FILTER(tbl_Class, MAX(tbl_Product[Percentages]) <= tbl_Class[Class Percentages]))
return
LOOKUPVALUE(tbl_Class[Class], tbl_Class[Class Order], vCount)

 

[srehman]

Hi JustynaMK,

Thanks a lot for your support.
Actually i am very excited to use your approach, unfortunately i stuck in step 2, I understood Logic but i am wondering in Step 2 mclass column i am getting 111111 for all rows which is not matching as per your snapshot. definitely i am doing some mistake.
As i highlighted with yellow color in a snapshot.

Would you please check where i am wrong.

 

[srehman]

Hi,

if am not much focus on step 2 , and follow step 3 , Obviously step 3 came from step 2 ,

copy your step 3 formula i can achieved results. which is awesome.

pls check snapshot.

 

[JustynaMK]

Amazing! Glad that it worked for you as per your first screenshot, it might be that the issue occurs because you have created both a "calculated column" and "measure" called mClass. Depending on which one you have used in your table, I'd expect "calculated column" to return 1 for each row, and "measure" to return proper results.

Anyways, I'm happy it all ended up working nicely. Have a great weekend!

 

[srehman]

Thanks again

 

[srehman]

Thanks for above solution it is working perfectly.

Sorry to bother you again, i have to create relationship between 2 tables based on above measures, It should connected by relationship.
I have watched ExcelFun videos he does it , but i could't.

I need to check each product how many times in ABC Class.

Any thoughts



Example

Class	AA	A	B	C	D
A1	1​		1​		1​
B1	1​			1​
B2		1​
C1	1​
C2				1​
C3	1​

 

[JustynaMK]

Good morning,

Unfortunately this approach won't work, as measures cannot participate in relationships. You would need to convert the measure into a calculated column in order to have it available in your Data Model. GIve it a try and let me know if you get stuck.