Filtering fact tables using inactive dimension tables

[klehar]

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

[PaulDBrown]

@klehar 

Create 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]

@klehar 

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 USERELATIONSHIP

2) 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.

[aj1973]

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

[klehar]

@aj1973 can you give an example dax code?

[MikeJohnsonZA]

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

[Jihwan_Kim]

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

[klehar]

@Jihwan_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.