Forum Discussion

TCavins's avatar
TCavins
Helper V
3 years ago

Measure Filter

I've combined 2 datasets into one for a map in Power BI. Lets call them People and Centers. I've added a column called IsPerson, setting it to either 1 or 0 based on the dataset.

 

Here's a sample of a table I created for testing. Value1 represents my InPerson column that I created in the datasets.

Table 2 = {
    (1, "Red", "Car", , ,),
    (1, "Blue", "Truck", , ,),
    (1, "Red", "Motorcycle",,,),
    (1, "Blue", "Car",,,),
    (0,,,"Group 1","County A", "Some Center Value"),
    (0,,,"Group 1", "County B","Center only value"),
    (0,,,"Group 2", "County A", "Center value goes here")
    }


I want slicers to be able to filter Value 2 and Value 3 for people. Then other slicers will filter Value 4 and Value 5 for Centers.

If I filter on "Red" and "County A", I want rows 1,3,5, and 7 in the results.

 

If I filter on "Red" and "Motorcycle", I want rows 3,5,6, and 7.

"Blue", "Group 2", I want rows 2 and 7.

"Group 2", I want rows 1,2,3,4, and 7.

 

Any ideas?

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    TCavins , You need to create independent tables with distinct values value 2 and value 3 and use them in slicer and then create measure like this and use it value 2 and value 3 or original table

     

    countrows(filter(Table, Table[Value2] in values('value 2'[Value 2]) || Table[Value 3] in values('value 2'[Value3]) ) )

     

    Need of an Independent Table in Power BI - Exclude: https://youtu.be/lOEW-YUrAbE

    • TCavins's avatar
      TCavins
      Helper V
      3 years ago

      Would it be similar to this since I only want if it's selected?

      MeasureFilter =
      COUNTROWS(FILTER('Table 2', 'Table 2'[Value2] IN ALLSELECTED(val2s[Value2]) || 'Table 2'[Value3] IN ALLSELECTED(val3s[Value3]) || 'Table 2'[Value4] IN ALLSELECTED(val4s[Value4])|| 'Table 2'[Value5] IN ALLSELECTED(val5s[Value5])))

      From there, how do filter out the original table to be displayed?
      • TCavins's avatar
        TCavins
        Helper V
        3 years ago

        amitchandak I think I'm close. The following works if I have filtered either value 2 or 3 and filtered value 4 or value 5. Any other combinations don't seem to work.

        I placed this measure on a visual level filter and set it = 1 but selected just "Blue" doesn't work. Selected "Blue" and "Car" doesn't work. "Blue" and "Group 1" works. "Blue", "Car", and "Group 1" works. Just "Group 1" doesn't work, etc.

        MeasureFilter = 
//COUNTROWS(FILTER('Table 2', 'Table 2'[Value2] IN ALLSELECTED(val2s[Value2]) || 'Table 2'[Value3] IN ALLSELECTED(val3s[Value3]) || 'Table 2'[Value4] IN ALLSELECTED(val4s[Value4])|| 'Table 2'[Value5] IN ALLSELECTED(val5s[Value5])))

    IF(
        (
            (
                SELECTEDVALUE('Table 2'[Value2]) IN ALLSELECTED(val2s[Value2])  || NOT(ISFILTERED(val2s[Value2]))
            )
            &&
            ( 
                SELECTEDVALUE('Table 2'[Value3]) IN ALLSELECTED(val3s[Value3]) || NOT(ISFILTERED(val3s[Value3]))
            )
        )
        ||
        (
            ( 
                SELECTEDVALUE('Table 2'[Value4]) IN ALLSELECTED(val4s[Value4]) || NOT(ISFILTERED(val4s[Value4]))
            )
            &&
            ( 
                SELECTEDVALUE('Table 2'[Value5]) IN ALLSELECTED(val5s[Value5]) || NOT(ISFILTERED(val5s[Value5]))
            )
        ),
        1,
        0
    )