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

 

      

 

 

Need Help
Tips and Tricks

9 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    3 years ago

    Anonymous,

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

      • DataInsights's avatar
        DataInsights
        Icon for Super User rankSuper User
        3 years ago

        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 )
    )
)
  • LQuedas's avatar
    LQuedas
    Icon for Resolver II rankResolver II
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello, here they are. Thank  you

       

      Qté_BOM = SUM('Z_BOM_Explosé'[Qté])
      Ventes = SUM(Ventes[Ventes])
  • 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