Forum Discussion

JoannaSK's avatar
JoannaSK
Microsoft Employee
6 months ago
Solved

Is row context value in filter context list

I have a table visual based on Fact_SpendDetails. I would like to only show rows where Fact_SpendDetails[DIM_OutsourcedID] is in Fact_BalanceHeadCountDetails[DIM_OutsourcedID], considering the curre...
Need Help
  • QuentinGa's avatar
    QuentinGa
    6 months ago

    Hi,


    I don’t know if this will work for your case, but you can try creating this measure and adding it to your filter visual (filter condition=1):


    Filter_Visual =
    IF(
    CALCULATE(
    COUNTROWS(Fact_BalanceHeadCountDetails),
    TREATAS( VALUES(Fact_SpendDetails[DIM_OutsourcedID]), Fact_BalanceHeadCountDetails[DIM_OutsourcedID] )
    ) > 0,

    1,

    0
    )

     

    Let me know if it works! If it does, please mark as solution and kudos are appreciated.

  • krishnakanth240's avatar
    krishnakanth240
    6 months ago

    Hi JoannaSK

     

    SELECTEDVALUE() function works when there is exactly one value in context. In a table visual, filter context may contain multiple values or blank.
    Can you try this approach
    Show Row =
    VAR _CurrentOID= MAX(Fact_SpendDetails[DIM_OutsourcedID])
    VAR _ValidOIDs= CALCULATETABLE(VALUES(Fact_BalanceHeadCountDetails[DIM_OutsourcedID]))
    RETURN
    IF(_CurrentOID IN _ValidOIDs,1,0) 

JoannaSK's avatar
JoannaSK
Microsoft Employee
6 months ago

Both solutions worked, with some caveats.

  • The version with VARs was marginally faster.
  • Both versions failed (out of memory) if I tried to pull columns from other tables into the visual. I ended up having to make calculated columns using RELATED() in my fact table, so that the visual could include just fact table columns.