Measure to determine if an item is in the top 10 of most used items with certain filter

[Anonymous]

I'm new to PowerBI and need to create a PieChart based on a subset of a table, the subset contains only those rows that belong to one of the top 10 most occuring items with a certain value in said table.

In SQL it would be something like this:

Measure = myValue IN
(
  SELECT myValue FROM myTable
  WHERE myValue in
  (
      SELECT TOP 10 myValue, COUNT(1) as counter
      FROM myTable
      WHERE filterValue = 1
      GROUP BY myValue
      ORDER BY counter DESC
  )
)

How do I translate this problem with a measure with DAX/PowerBI?

I know it's not very effective to use a measure for this (recalculating the top 10 for every row..), but I don't have access to the database or PowerQuery so unless I'm missing an other alternative the measure is the only way.

I've been trying to use the summarize and filter functions, but everything I try leads to errors and undesired results. Any help would be appreciated!

[v-lili6-msft · Accepted solution]

hi, @Anonymous

There is a simple way for you refer to:

Use RANKX Function to create a measure like this:

Rank = RANKX(ALLSELECTED(Table1),CALCULATE(COUNTA(Table1[myValue])),,DESC )

or

Rank = RANKX(ALLSELECTED(Table1),CALCULATE(COUNTA(Table1[myValue]),filter(Table1, Table1[filterValue]=1)),,DESC )

Then drag it into visual level filter and set is less then or equal to 10

Result:

Best Regards,

Lin

[Anonymous]

Why you don't have access to power query? Are you working in direct query?

[Anonymous]

A third party destributes the data to us via Azure, but they blocked it so we cannot use those functionalities sadly.

[Anonymous]

Can you use Calculated table (new table on top banner)?