Forum Discussion

spenceratchison's avatar
spenceratchison
Regular Visitor
3 years ago
Solved

Dax to Ignore Specific Slicer

Hello, I am attempting to create a measure that is responsive to a specific slicer, but not another. I will include screenshots to illustrate my issue. As you can see, this is functioning pro...
Need Help
  • danextian's avatar
    danextian
    3 years ago

    Hi spenceratchison ,

     

    You will need to use a separate table that doesn't have a relationship (disconnected table) from your fact tables and then create a measure so  it is filtered according to what is selected in the column from that disconnected table.

     

    Disconnected Table =
//as a calcualted table
DISTINCT ( 'Table'[Slicer Column] )

     

    My Measure =
CALCULATE (
    [My measure],
    FILTER (
        'Table',
        'Table'[Slicer Column] = SELECTEDVALUE ( 'Disconnected Table'[Slicer Column] )
    )
)

OR

My Measure =
CALCULATE (
    [My measure],
    FILTER (
        'Table',
        'Table'[Slicer Column] IN VALUES ( 'Disconnected Table'[Slicer Column] )
    )
)

     

    Alternatively,

     

    You can still create a relationship between your fact table and that calculated table but keep it INACTIVE. Use USERELATIONSHIP to invoke the relationship between the two

    My Measure =
CALCULATE (
    [My measure],
    USERELATIONSHIP ( 'Disconnected Table'[Slicer Column], 'Table'[Slicer Column] )
)

     

danextian's avatar
danextian
Icon for Super User rankSuper User
3 years ago

Hi spenceratchison ,

 

You will need to use a separate table that doesn't have a relationship (disconnected table) from your fact tables and then create a measure so  it is filtered according to what is selected in the column from that disconnected table.

 

Disconnected Table =
//as a calcualted table
DISTINCT ( 'Table'[Slicer Column] )

 

My Measure =
CALCULATE (
    [My measure],
    FILTER (
        'Table',
        'Table'[Slicer Column] = SELECTEDVALUE ( 'Disconnected Table'[Slicer Column] )
    )
)

OR

My Measure =
CALCULATE (
    [My measure],
    FILTER (
        'Table',
        'Table'[Slicer Column] IN VALUES ( 'Disconnected Table'[Slicer Column] )
    )
)

 

Alternatively,

 

You can still create a relationship between your fact table and that calculated table but keep it INACTIVE. Use USERELATIONSHIP to invoke the relationship between the two

My Measure =
CALCULATE (
    [My measure],
    USERELATIONSHIP ( 'Disconnected Table'[Slicer Column], 'Table'[Slicer Column] )
)

 

WTAS80486's avatar
WTAS80486
Icon for Helper IV rankHelper IV
2 years ago

Hi danextian Could you help with this scenario

I need to use finish date and reported date from the same table. create a measure to calculate total orders so that it is not affected by finish date slicer but is affected by reported date. just the reverse for another measure to calculate completed orders(order status =COMP or CLOSE) . should change with finish date slicer  but not with reported date slicer. both these measures have to be used in a column chart to show total orders and completed orders