Forum Discussion
Helper VFiltering fact tables using inactive dimension tables
Hi,
I have stripped down my dataset to make my case understandable.
I have a relationship between product dimension and fact dimension. But this relationship is inactive(in my original dataset with a complicated model, i cannot connect these 2 tables becuase of ambiguous relationships)
Actual dataset error:
My goal here is to filter the sales table below using the product dimension column using an inactive relationship
Can this be done?
amitchandak please dont send links, if you have a solution only then reply
9 Replies
- Jihwan_Kim
Super User
Hi, klehar
Please correct me if I wrongly understood your question.
If you have an inactive relationship between two tables, using USERELATIONSHIP function is one way to create a measure that can be filtered by one dimension table.
Please try the below measure.
Sales =
CALCULATE (
SUM ( Fact[sales] ),
USERELATIONSHIP ( Fact[Product ID], Dimension[Product ID] )
)Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
kleharJihwan_Kim , I'm afraid userelationship cant be used.
I thought it can be. But all im trying to do is filter the fact using the dimension table with an inactive relationship.
- MikeJohnsonZA
Responsive Resident
Yes, this can be done but you will need to use an explicit measure for it (This is probably what you are doing already).
Sales =
CALCULATE (
SUM ( fact[sales] ),
USERELATIONSHIP ( dimension[product ID], sales[Product ID] )
)*To help reduce the model complexity and ambiguity, try to only use bidirectional relationships where absolutely necessary. (Sometime easier said than done)
If you have multiple measures in your report then each of them will need to apply the same pattern.
- klehar
MikeJohnsonZA I dont want to calculate a measure so crossfilter and userelationship may not work
I just want to filter the sales table using my dimension table without an active join
- aj1973
Community Champion
Hi klehar
To be able to use Product column as a slicer in your model you need to add a Calculated column (slicers don't accept measures) to the fact table using the inactive relationship if it exist. You can also use Crossjoin to navigate between tables and to build your calculated column.
Here is a video that can help solve the ambiguity if you want
https://www.youtube.com/watch?v=x3m7qzsVJqQ
PaulDBrownCreate a measure along the lines of
Filter using Userelationship = COUNTROWS ( CALCULATETABLE ( VALUES ( Fact[ProductID] ), USERELATIONSHIP ( 'Dimension'[ProductID], Fact[ProductID] ) ) )or
Filter using TREATAS = COUNTROWS ( TREATAS ( VALUES ( Dimension[ProductID] ), Fact[ProductID] ) )
Select the visual, add this measure to the filters on the visual in the filter pane and set the parameter to "is 1".- klehar
Can you also explain what this does.
Btw syntax is incorrect somewhere
- PaulDBrown
Regarding the syntax, adjust the table and column references to your model (for example use Product ID instead of my ProductID)
As regards what they do:
[Filter using userelationship]
1) CALCULATETABLE returns a table of unique values for fact[ProductID] based on the filter enacted between the dimension table and fact table using USERELATIONSHIP2) COUNTROWS returns the count by row
[Filter using Treatas]
1) TREATAS creates a virtual relationship between the columns contained within the expression to return a table of ProductID values.
2) COUNTROWS returns the count by row
When you add either of these measures to the filter pane and establish the parameter as 1, the visual will be filtered to display only the values selected in the slicer.
