Forum Discussion
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 current filter context (slicers on Common_ReportsToHierarchyHRDP[ReportsToAliasL3], where Common_ReportsToHierarchyHRDP[DIM_EmployeeID] has a one-to-many relationship to the headcount fact table).
This is a DirectQuery connection to a cube I do not own, so I can't add or change relationships between cube tables. There is no relationship between Fact_Spend and Fact_BalanceHeadCountDetails. There is no relationship between Fact_Spend and Common_ReportsToHierarchyHRDP.
I tried to create a linking table, with DIM_EmployeeID and DIM_OutsourcedID from Fact_BalanceHeadCountDetails, and created a one to many relationship between Common_ReportsTo and Linking, but when I tried to create a many-to-many relationship between Linking and Fact_Spend, it gave me an error saying that I was filtering two tables from the same data source through a path that exists outside of the data source.
I don't know how to create a calculated column that takes filter context into account.
I created the below calculated measure, but it seems to return FALSE for everything.
Xref =
VAR HC_OID = DISTINCT( SELECTCOLUMNS( Fact_BalanceHeadCountDetails, "OID", [DIM_OutsourcedID] ) )
RETURN
SELECTEDVALUE(Fact_SpendDetails[DIM_OutsourcedID]) in HC_OIDHow do I get slicers that only have relationships to Fact_BalanceHeadCountDetails to filter my table visual that is based on Fact_SpendDetails?
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.
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)
3 Replies
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.
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)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.
