Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Using a column from another Table

Hi everyone, 

 

I'm trying to use two rather simple table to make a report, here is the steps I've done so far after loading my data:

1- set a relation between two tables (Between: Tableau2['fournisseur + month'] and Qtité_livré[Fourn. & Month])

 2- Create a measure calculated using both tables:  

NQ (ppm) = COUNT([Date de récéption de l'IC]) / calculate(sum('Qtité_livré'[Nbre de commandes])) * 1000000
 
3- While trying to vizulise my data i got this problem: 
 my new mesure (NQ (ppm)) isn't calculated the way I want it to be

And due to the fact that it's using the sum of "Nbre de commandes" across the Table 'Qtité_livré' instead of using it in its context 

To be more clear, I get the right values when i add the column 'Qtité_livré'[Fourn.. & Month] to the table above ===> 'Qtité_livré'[Fourn.. & Month] being the right context to this mesure

 

 

Thank you for your help in advance

 
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Anonymous 

    I thought DAX could not handle this situation, but after I studied your data structure again, I found that DAX can still handle it. 

    The principle of DAX is to take the intersection with the red part in the figure, and then sum it.

    Dax code:

    test = //measure
    var _t1= SELECTCOLUMNS(FILTER(ALL(Tableau2),Tableau2[Année]=MIN(Tableau2[Année]) && Tableau2[Mois]=MIN(Tableau2[Mois])),"s",Tableau2[fourniseur + month])
return CALCULATE(SUM('Qtité_livré'[Nbre de commandes]),INTERSECT(VALUES('Qtité_livré'[Fourn. & Month]),_t1))

     

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Anonymous  try this and see if it helps 

     

    apply the following

     

     

    on

     

    any axis

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you smpa01  for you response. 

    When I tried it (Apply dont't Summarize to "Nbre de commandes"), this is what it shows :

    But I need to have one line with 183 as total (the sum) instead of the 3 lines in blue

    • smpa01's avatar
      smpa01
      Community Champion
      4 years ago

      Anonymous  is it possible to provide a sample pbix?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hello, 

         

        Apparently as a new memeber I cannot share pbix files yet... 

         

        Here's a link via Wetransfer: https://we.tl/t-ZzhcS6QdZk

        Thanks

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi Anonymous 

    Would you consider using M code?

     

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello v-xiaotang , 
      I don't mind using it. what d o you have in mind ?

      • v-xiaotang's avatar
        v-xiaotang
        Community Support
        4 years ago

        Hi Anonymous 

        I thought DAX could not handle this situation, but after I studied your data structure again, I found that DAX can still handle it. 

        The principle of DAX is to take the intersection with the red part in the figure, and then sum it.

        Dax code:

        test = //measure
    var _t1= SELECTCOLUMNS(FILTER(ALL(Tableau2),Tableau2[Année]=MIN(Tableau2[Année]) && Tableau2[Mois]=MIN(Tableau2[Mois])),"s",Tableau2[fourniseur + month])
return CALCULATE(SUM('Qtité_livré'[Nbre de commandes]),INTERSECT(VALUES('Qtité_livré'[Fourn. & Month]),_t1))

         

         

        Best Regards,

        Community Support Team _Tang

        If this post helps, please consider Accept it as the solution to help the other members find it more quickly.