Top N filter on card visual not working on direct query mode.

[MeetRa100]

Hi everyone,
I am trying to add a top n filter on a card visual in pbi desktop. The requirement is to do this with the help of measure not parameter. And after a lot of tests, I was not able to compete this simple task, result is not showing correctly.
And I can’t change data from direct query to import.

The option fields below "Visual level filter" were non-responsive. I couldn’t interact with any of the "Show items when the value:" options. The drop-down menu wouldn't drop down, the radio buttons didn't response to mouse clicks, and I couldn't type into either of the text fields above and below the drop-down.

I wanted to show same grand total value (Rate Efficiency %) – 74.45 in card, what it is showing in Matrix/Table.

The card was displaying an average of values in a column. The values were numbers. I tried to set it to count only Top N values.

Data Explanation: All the columns are coming from different table.

Table 1 – Plant Name

Table 2 – Machine Name

Table 3 – Parts Name

Table 4 – Sales Order (This is a measure, and I can’t see the formula as of it)

Table 5 – Rate Efficiency % (This is a measure, and I can’t see the formula as of it)

Requirement:

• 1 static measure for Top N count
• Use this measure for filtering Top N Parts Name based on Sales Order
• On page, show:
• 1 slicer (Contains Plant name & Machine name),
• Matrix (Contains Rows -Plant name, machine name, Part Name, Table, Values: Sales Order, Rate Efficiency %),
• Card (Contains Rate Efficiency %) - Rate Efficiency % of Top N Parts, same as grand total of Rate Efficiency in Matrix),

Solution what I have done:

Top N Reference Measure = 3

Rank = RANKX(ALL('Part Targets'[Parts Name]),[Sales Order],,DESC,Dense)

Part_rank_within_selectedN = IF([Rank]<=[Top N Reference Measure],1,0)

These measures are perfectly working fine with all the visuals except card because I am unable to filter it out.

[some_bih]

Hi @MeetRa100 check link, part below if applicable for your case

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

[Anonymous]

Hi @MeetRa100 ,

You may consider choose this icon in Options:

Add a new measure:

Top N Parts Total Rate =
SUMX (
    FILTER ( ALL ( 'Part Targets' ), [Part_rank_within_selectedN] = 1 ),
    [Rate Efficiency %]
)

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MeetRa100]

@Anonymous I am unable to take below column(which is a measure) , PBI is not allowing me to add this measure inside a new mesure what you are suggesting.

[Part_rank_within_selectedN]