Power BI - Creating a measure as a Visual Level Filter

Question

I have a requirement, where I have data like this.

ColA ColB ColC ColD
A    A     C    1 
B    C     C    1
A    B     C    3
C    D     C    2

I have one visual as a table with just Col A and Col B generated through the Min Number on Col D.

ColA ColB  
A    A     
B    c

I want to generate another visual table that takes these ColA Values "A" and "B" and filters the actual data table.

  ColA ColB ColC ColD
    A    A     C    1 
    B    C     C    1
    A    B     C    3

I am trying to solve this using AllSelected Function but it is not helping me. How can we use a measure on a visual level filter to solve this problem.

Alexis Olson Alexis Olson · Accepted Answer · 2018-08-22T14:34:04

You can create a filtering measure like this:

FilterMeasure = 
    VAR MinD = CALCULATE(MIN(Table2[ColD]), ALLSELECTED(Table2))
    VAR AVals = CALCULATETABLE(VALUES(Table2[ColA]),
                    ALLSELECTED(Table2),
                    Table2[ColD] = MinD)
    RETURN IF(MAX(Table2[ColA]) IN AVals, 1, 0)

First, it finds the minimal number in ColD. Then it finds the values in ColA that are associated with that ColD value. Finally, it returns 1 if the ColA value in the row of your table visual is one of the ones from the previous step and 0 otherwise.

Power BI - Creating a measure as a Visual Level Filter

1 Answers