Forum Discussion

ViralPatel212's avatar
ViralPatel212
Resolver I
1 year ago
Solved

Measure to Identify Values in table when filtered

Hello Team,

 

I have a visual where im using the "counter_party_ column from a table called "Counterparty". I am trying to create a dax or a calcualted column to identify the following issue:

*Please note the interaction between slicer and visual table is turned off (this has to happen)

 

1) If a value from the slicer is selected than is should only display a 1 on that value in the  visual table and the rest as 0 

If you select "ABN Amro" in the slicer:

counter_party Identify
ABG Sundal Collier0
ABN Amro (Selected)1
Academy Securities0
ADCAP Securities0

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Identify Measure: 

Identify =
VAR SelectedCounterparty = SELECTEDVALUE(Counterparty_tbl[counter_party])

RETURN
IF(
    NOT(ISBLANK(SelectedCounterparty)) &&
    LOOKUPVALUE(Counterparty_tbl[counter_party], Counterparty_tbl[counter_party], MAX(Counterparty_tbl[counter_party])) = SelectedCounterparty,
    1,
    0
)
 
Hope this is possible!
 
Thanks
 

 

 

Need Help
Show and Tell
Tutorial Requests
  • johnt75's avatar
    johnt75
    1 year ago

    If you just need it for one measure, you could use something like

    My Measure = CALCULATE(
    SUM( 'Table'[Value] ),
    TREATAS( VALUES( Counter party slicer[counter_party] ), Counterparty_tbl[counter_party] )
)

      replacing the SUM with whatever calculation you needed.

    If you want to apply it to multiple measures you could use a calculation group and create a calculation item like

    Calc Item = CALCULATE(
    SELECTEDMEASURE(),
    TREATAS( VALUES( Counter party slicer[counter_party] ), Counterparty_tbl[counter_party] )
)

    and then apply that calc item as a filter on the visuals you want to impact.

6 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    There's no way to do this with interactions turned off, as the table / matrix visual in which the measure is executed has no access to the slicer.

    You can create a disconnected table to use in the slicer like

    Counter party slicer = VALUES( Counterparty_tbl[counter_party] )

    Then create a measure like

    Identify =
IF (
    SELECTEDVALUE ( Counterparty_tbl[counter_party] )
        = SELECTEDVALUE ( 'Counter party slicer'[counter_party] ),
    1,
    0
)
    • ViralPatel212's avatar
      ViralPatel212
      Resolver I
      1 year ago

      johnt75 Thank you for that. Is there a way that to use a disconnected table but use that table to filter context in the counterparty_tbl? becuase that works but if i create a relationship it then breaks?

      • johnt75's avatar
        johnt75
        Super User
        1 year ago

        Not within visuals, but you could make it have an effect in measures by using

        TREATAS( VALUES( Counter party slicer[counter_party] ), Counterparty_tbl[counter_party] )
  • ViralPatel212's avatar
    ViralPatel212
    Resolver I
    1 year ago

    johnt75  You sir are a Genius!! i was able to add this as a variable and the called it in the measure!

     

    TREATAS( VALUES( Counter party slicer[counter_party] ), Counterparty_tbl[counter_party] )