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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
thedocs
Helper I
Helper I

Filter on measure changes original values

In below example I need to see the top 14 using the Rank Measure. The value 14 is also a Measure. When I pass the measure to the Rank measure it afects the filter. How do I filter Top value when value is a Measure? Any help much appreciated

thedocs_0-1691100880905.png

Rank =

RANKX(ALL( ‘Name’),CALCULATE ( SUMX ( 'Name', [countid] + 'Name'[Index] )), ,DESC,Dense)

 

Top Qty =

CALCULATE(DISTINCTCOUNT(name)) / 2)

 

1 ACCEPTED SOLUTION

Because of the row context in the visual.  For that row in the visual, the distinctcount of that Customer will be 1 itself.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
thedocs
Helper I
Helper I

thank you for all your help

thedocs
Helper I
Helper I

That works a treat thank you 🙂 (but only when I pass a fixed value to the measure.)

 

Any idea why when I pass a calculated measure [Top Customers] to  [Measure Top] it returns 1 row instead of the 11 that is calculated? How can I pass the total 11 not 1 row value?

 

thedocs_0-1691420432848.png

Top Customers = DISTINCTCOUNT(Leads[Customer Name])/2
 
Measure Top = calculate([countleadid],topn([Top Customers],all(Leads[Customer Name]),[countleadid],DESC),values(Leads[Customer Name]))
 

Because of the row context in the visual.  For that row in the visual, the distinctcount of that Customer will be 1 itself.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
thedocs
Helper I
Helper I

Hi Ashish,

 

this will only reduce the number of Top customers not display the bottom, can only be achieved with Rank which I have done. I am just struggling to pass a calculated measure to the simple flag measure on original pbix.

 

I thought this part would have been simple to pass the quantity, this should have been the easy bit ...

 

My measure should display the bottom ranking Merchants.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
thedocs
Helper I
Helper I

Hi Ashish,

thank you very much for taking the time to reply.

 

In your .pbix you have defined the top customers. I already have a measure that does this called Rank. In my pbix example I am trying to calculate the total number of Top Ranked Customers /2 then pass this result to the measure (Flag Qty).

 

Basically in my .pbix I need to:

 

Replace

Total Customers to View = 6

 

With

Total Customers to View = (total customers /2)

 

This measure will need to work with ‘Flag Qty’ which I can then use to filter the result. I need to use the existing Rank measure as there is no BOTTOMN in DAX.

many thanks again,

Connor

Hi,

If you want the Bottom n customers, then try this

Measure = calculate([Total],topn([Top qty]/2,all('Name'[Merchant]),[Total],ASC),values('Name'[Merchant]))

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
thedocs
Helper I
Helper I

I have attached a .pbix which will make it clearer

 

https://drive.google.com/file/d/16XQ7j8LNsi2KMeKZsYSkGp8mJGyawb0e/view?usp=sharing

 

What I need is to replace  ( Total Customers to View = 6 )

 

With ??     Total Customers to View = Calculate(DISTINCTCOUNT('Leads'[Customer Name])/2

 

So result will be same with 6 passed to the second measure

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1691192644562.png

 

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

Try these measures

Total = SUM('Name'[countid],'Name'[Index]
Measure = calculate([Total],topn([Top qty],all('Name'[Merchant]),[Total]),values('Name'[Merchant]))

Hope this helps.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
foodd
Super User
Super User

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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