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 ...
Need Help
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
    )

       

      • TCavins's avatar
        TCavins
        Helper V
        3 years ago

        Initial testing of the following seems to work:

        MeasureFilter = 

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