Activating inactive relationship / virtual relationship versus active relationship

[BIanon]

Hello Community,

Please consider the following relationships between these tables:



And then consider then following DAX measure:

COUNTROWS(
    		DISTINCT(
        		UNION(
        			VALUES(Fact[UserId])
			        , CALCULATETABLE(VALUES(Fact[UserId]), ALL('Product'), TREATAS(VALUES('Product2'[Id]), Fact[Id]))
			        , CALCULATETABLE(VALUES(Fact[UserId]), ALL('Product'), TREATAS(VALUES('Product3'[Id]), Fact[Id]))
			        )
			    )
			)

This measure return a scalar value which is a count of the distinct rows present in the combination of the three calculated tables.



And here comes my issue, I need to deactivate the relationships between Category and Product2/Product3 to achieve relationships like this:



After deactivating the two relationships I then attempt to propagate the filter context by calling either TREATAS() or USERELATIONSHIP() like the following examples:

COUNTROWS(
    		DISTINCT(
        		UNION(
        			VALUES(Fact[UserId])
			        , CALCULATETABLE(VALUES(Fact[UserId]), ALL('Product'), TREATAS('Category'[Id], 'Product2'[Id]), TREATAS(VALUES('Product2'[Id]), Fact[Id]))
			        , CALCULATETABLE(VALUES(Fact[UserId]), ALL('Product'), TREATAS('Category'[Id], 'Product3'[Id]), TREATAS(VALUES('Product3'[Id]), Fact[Id]))
			        )
			    )
			)

COUNTROWS(
    		DISTINCT(
        		UNION(
        			VALUES(Fact[UserId])
			        , CALCULATETABLE(VALUES(Fact[UserId]), ALL('Product'), USERELATIONSHIP('Category'[Id], 'Product2'[Id]), TREATAS(VALUES('Product2'[Id]), Fact[Id]))
			        , CALCULATETABLE(VALUES(Fact[UserId]), ALL('Product'), USERELATIONSHIP('Category'[Id], 'Product3'[Id]), TREATAS(VALUES('Product3'[Id]), Fact[Id]))
			        )
			    )
			)

 
Unfortunately, these two attempts produce vastly different results than having the relationship active and I cannot for the life of me figure out why.

Hoping for some ideas on how to proceed testing this and understanding the case at hand.

Thank you

[tt_and]

Hi. Sorry for not giving you a direct answer for the problem you are facing. I would instead highly recommend you to think differently about modelling. Either by merging all the products into one table using an extra column for "Product type" with values "1,2,3" or at least to move the category to a column in each product table. This way you can have a direct relation to all items between product and fact. If you have same ID for products in the different tables, making a column with surrogate key will give you unique IDs.
E.g: Product 1450 in table 1 and product 1450 in table 2 will have the value 1.1450 and 2.1450 in a ProductSK column.

[BIanon]

Hello,

This example is just a small part of a large model. While I appreciate that you are trying to help, it is not applicable in this situation and your assumptions about the contents and use of the tables are wrong.

This setup is created to enable a very specific type of comparative analytics that is commonly done within my data domain which PBI inherently has a hard time producing.

Thanks. 🙂