DAX: Using Measure as a slicer and count total

1
votes

I have a table where it shown the items and its frequency.

+---------+-------+
| ITEMS   | FREQ  |
+---------+-------+
| apple   |     7 |
| banana  |     5 |
| carrot  |     8 |
| potato  |     8 |
+---------+-------+

Frequency is obtained by using DAX measure.

Frequency = CALCULATE(COUNT('Tbl1'[Item]), NOT('Tbl1'[Item Status]) IN {"BAD"})

I want to create a card visual that depending on the chosen frequency, will show the total number of items. For example Freq 7 = 1, Freq 8 =2. I tried to use Frequency as a slicer, but its not working. Is there any ways to achieve this?

1
powerbidax

1 Answers

1
votes

You need to create a separate table to use as a frequency slicer.

Define a new calculated table with no relationships to other table:

Frequencies =
VAR MaxFreq = MAXX ( ALL ( Tbl1[Item] ), [Frequency] )
RETURN
    GENERATESERIES ( 0, MaxFreq )

You can then use the column Frequencies[Value] as a slicer and define a new measure to count the number of items with frequency matching the frequency selected via this slicer as:

FreqCount =
VAR Freq = SELECTEDVALUE ( Frequencies[Value] )
RETURN
    COUNTROWS ( FILTER ( VALUES ( Tbl1[Item] ), [Frequency] = Freq ) )