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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to filter a DAX calculation using a calculated measure

Hello,

 

My objected is two parts (1) Calculate a dynamic ranking for a basic matrix (2) Create individual measures that return the name and profit for a specific rank (e.g. rank 2).

 

(1) Created Dynamic ranking calculated measure with following equation:

 

Product Profit Rank = RANKX(ALLSELECTED(Orders[Product Name]),CALCULATE(SUM(Orders[Profit])),,DESC)
 
Annotation 2020-09-02 111802.png

 

As you can see, it worked. And as I change the filters on the page, it dynamically adjusts.
 
(2) Attempted to create individual measures that highlight a specific rank, but am having issues. I would like a measure that returns the name of 'n' ranked product and a second that returns the profit of 'n' ranked product. Here are the DAX calculatons I think should work, but they are not. It seems that the filter portion is what is having issues, so just the first nonblank is returned or the profit for all products. Your help would be apprecaited. Thanks!
 
Rank 2 Product Name = CALCULATE(FIRSTNONBLANK(Orders[Product Name],1),FILTER(Orders, [Product Profit Rank] = 1))
 
Rank 2 Product Profit = CALCULATE(SUM(Orders[Profit),FILTER(Orders, [Product Profit Rank] = 1))
1 REPLY 1
amitchandak
Super User
Super User

@Anonymous , refer

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

 

, refer my rank

City Rank = RANKX(all(Geography[City]),[Sales])

 

filters I tried

Rank Top 10= CALCULATE(if([City Rank]<=10,[Sales],BLANK()) ,VALUES(Geography[City]))
Rank Top 10 1 = sumx(VALUES(Geography[City]),if([City Rank]<=10,[Sales],BLANK()) )
Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))
Rank 2nd top = sumx(filter(VALUES(Geography[City]),[City Rank]=2),[Sales] )

 

https://databear.com/power-bi-dax-topn-function/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors