CROSSFILTER without match

[Anonymous]

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 empty value in the table below to the right. I've tried multiple things with "If hasonevalue" and >0 to try to remove it, but the blank keeps showing up. Is there a way to remove it by adding something in the measure ? Thank you

Qté_MP =
CALCULATE (
         [Ventes] * [Qté_Bom],
         CROSSFILTER ( Produits[Produit], 'Z_BOM_Explosé'[Produit], BOTH ))

      

[LQuedas]

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

[LQuedas]

Hey @Anonymous 

can you detail the two measures you are using in the calculation function? i.e.,  what are the DAX expressions used to calculate [Ventes]  and [Qté_Bom]?

Tks, LQ

[Anonymous]

Hello, here they are. Thank  you

Qté_BOM = SUM('Z_BOM_Explosé'[Qté])

Ventes = SUM(Ventes[Ventes])

[DataInsights]

@Anonymous,

Is your visual using the Produit field in the dimension table Produits? Visuals should use fields in dimension tables whenever possible.

[Anonymous]

Hello, yes it's from the "Produits" table. Thank you 

[DataInsights]

@Anonymous,

You could try testing for blank before performing the calculation:

Qté_MP =
IF (
    NOT ISBLANK ( [Ventes] ) && NOT ISBLANK ( [Qté_Bom] ),
    CALCULATE (
        [Ventes] * [Qté_Bom],
        CROSSFILTER ( Produits[Produit], 'Z_BOM_Explosé'[Produit], BOTH )
    )
)

[Anonymous]

Hello, yes I already did but it wasn't working. Thank you

[DataInsights]

@Anonymous,

Why are you using the CROSSFILTER function? Not sure why you would need to pass filter context from Z_BOM to Ventes, since they share the dimension table Produits.

[Anonymous]

Hello, I tried to simply the example to make it easy to understand, but I'm going to have other columns my result table (# Commande and Ventes from Ventes, Date from Dates, MP and Qté from Z_BOM_Explosé). So the blank rows multiply and I have a rather large dataset so I get millions of useless rows.

Thank you