Forum Discussion

smileamile2's avatar
smileamile2
Resolver I
1 year ago
Solved

Slicer DAX

Error
Need Help
  • smileamile2's avatar
    smileamile2
    1 year ago

    Solution implemented was using default functionality of the new slicer visual. 

danextian's avatar
danextian
Super User
1 year ago

Hi smileamile2 

 

You can unpivot all other columns but Color in the query editor so Status and State values are in one column or you can use a disconnected table and use a measure as a visual filter.

Filter = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    KEEPFILTERS (
        'Table'[Status]
            IN VALUES ( StatusOrState[Value] )
                || 'Table'[State] IN VALUES ( StatusOrState[Value] )
    )
)

 

Please see the attached sample pbix.

  • danextian's avatar
    danextian
    Super User
    1 year ago

    HI smileamile2 

     

    Please update your formula to this:

    Filter = 
VAR _VALUES =
    UNION ( VALUES ( StatusOrState[Value] ), { BLANK () } )
VAR _VALUES2 =
    VALUES ( StatusOrState[Value] )
RETURN
    IF (
        NOT ( ISFILTERED ( StatusOrState[Value] ) ),
        CALCULATE (
            COUNTROWS ( 'Table' ),
            KEEPFILTERS ( 'Table'[Status] IN _VALUES || 'Table'[State] IN _VALUES )
        ),
        CALCULATE (
            COUNTROWS ( 'Table' ),
            KEEPFILTERS ( 'Table'[Status] IN _VALUES2 || 'Table'[State] IN _VALUES2 )
        )
    )

    Please see updated pbix.

    • smileamile2's avatar
      smileamile2
      Resolver I
      1 year ago

       

      x

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Thanks for danextian and Ashish_Mathur's concern about this issue.

         

        Hi, smileamile2 

         

        Please try the following DAX:

        Filter = 
VAR _VALUES =
    UNION ( VALUES ( StatusOrState[Value] ), { BLANK () } )
VAR _VALUES2 =
    VALUES ( StatusOrState[Value] )
RETURN
    IF (
        NOT ( ISFILTERED ( StatusOrState[Value] ) ),
        CALCULATE (
            COUNTROWS ( 'Table' ),
            KEEPFILTERS ( 'Table'[Status] IN _VALUES || 'Table'[State] IN _VALUES )
        ),
        IF (
            COUNTROWS ( _VALUES2 ) = 1,
            CALCULATE (
                COUNTROWS ( 'Table' ),
                KEEPFILTERS ( 'Table'[Status] IN _VALUES2 || 'Table'[State] IN _VALUES2 )
            ),
            CALCULATE (
                COUNTROWS ( 'Table' ),
                KEEPFILTERS ( 'Table'[Status] IN _VALUES2 && 'Table'[State] IN _VALUES2 )
            )
        )
    )

         

        Output:

         

         

         

        I have attached the pbix file for that example below, I hope it helps!

         

         

        I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
        Best Regards,
        Fen Ling,
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.