Using a column from another Table

[Anonymous]

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

[v-xiaotang]

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]

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

[v-xiaotang · Accepted solution]

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.

[Anonymous]

Hello @v-xiaotang , 

Well that does it. Thank you !

[Anonymous]

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]

@Anonymous  is it possible to provide a sample pbix?

[Anonymous]

Hello, 

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

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

Thanks

[smpa01]

@Anonymous  try this and see if it helps 

apply the following

on

any axis