Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

CROSSFILTER without match

Hello, I'm using a simple cross filter on the tables below. However, not all my products are matching for the three tables (Some appear only in Z_BOMS, other only in Ventes, etc..). This returns an e...
Need Help
Tips and Tricks
LQuedas's avatar
LQuedas
Icon for Resolver II rankResolver II
3 years ago

Hi Anonymous ,

 

Yes, I agree with DataInsights , you don't need to use the CROSSJOIN function... 

I created the following example for you, it might help you to figure the solution you need...

1) Created a model with 2 fact tables and one Product Dimension, Not all the product have values in both fact tables, e.g. Prudct F only have value in Fact 2  and Product D only have value in FAct1

 

2) create to basic measures:

 
Total Fact1 = sum(Fact1[Fact1_Value])
Total Fact2 = sum(Fact2[Fact2_Value])
 

3) create a composed measure that will check if the basic measures have value in the context, if not replace the value of the measure by 1 to allow the multiplication.

 

FactTotal =
var _fact1=if(ISBLANK(Fact1[Total Fact1]),1,Fact1[Total Fact1])
var _fact2=if(ISBLANK(Fact2[Total Fact2]),1,Fact2[Total Fact2])
return _fact1*_fact2
 
using a table to show the FactTotal by Product Name (Product Name is the attribute of the Product dimension).

 

 Hope this example helps 😉
 
Cheers, LQ