Filter on measure changes original values

[thedocs]

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

Rank =

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

Top Qty =

CALCULATE(DISTINCTCOUNT(name)) / 2)

[thedocs]

thank you for all your help

[thedocs]

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?

Top Customers = DISTINCTCOUNT(Leads[Customer Name])/2

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

[Ashish_Mathur · 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.

[thedocs]

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 ...

[Ashish_Mathur]

My measure should display the bottom ranking Merchants.

[thedocs]

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

[Ashish_Mathur]

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]))

[thedocs]

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

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Ashish_Mathur]

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.

[foodd]

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.