Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
LudivineLOU
Helper I
Helper I

problem with group /filter

Hello Everybody, 

 
 
 

I have 2 tables : 

Table ZE : 

LudivineLOU_0-1598344726729.png

 

Table GR :

table Gr.png

 
 
MAXConfdate = CALCULATE(MAX(ZE[MAXdateZE]);FILTER(ZE;ZE[Pur. Doc. Itm]=GR[Pur. Doc. Itm]&&ZE[Pur. Doc. Itm]=GR[Pur. Doc. Itm])) --> comes from the tableZE
QTYCONFIRMEDZE = CALCULATE(SUM(ZE[SUMQTYZE]);FILTER(ZE;ZE[Pur. Doc. Itm]=GR[Pur. Doc. Itm]))
--> comes from the tableZE
 

I would like create a model like this : 

 

LudivineLOU_2-1598344888994.png

 

Mois : Maxconfdate  --> By month (table GR)

GR PC (table GR)

2 measures :

QTY_conf = CALCULATE(SUMMARIZE(ZE;ze[SUMQTYZE]);FILTER(ZE;ZE[Pur. Doc. Itm]=MAX(GR[Pur. Doc. Itm])))

qtyconf2 = var __table =SUMMARIZE(gr;GR[MAXConfdate];"__value";[QTY_conf]) return IF(HASONEVALUE(GR[MAXConfdate]);[QTY_conf];SUMX(__table;([__value])))   
--> It is to have the correct total (5100pcs)
 
Unfortunetaly, as you can see, the result for september is wrong : 
I have 150pcs instead of 550pcs.
It is because it takes into account the Purch item number. 
What is the formula to avoid to take into account the purch item number ?
 
Thank you, 
Best,
 
1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @LudivineLOU ,


I don't quite understand what value you want. May I ask how 550 is calculated? If you calculate the total, September should be 450.

1.png

 

QTY_conf = CALCULATE(SUM(ZE[SUMQTYZE]),FILTER(ZE,ZE[Pur. Doc. Itm]=MAX(GR[Pur. Doc. Itm])))

 

This measure you give is the max value of Purch item number. I filter the data for January as shown below, 800 is the maximum value, not the sum.

2.png

3.png

I found the PBI file from your other posts, and I found that there is no relationship between your two tables. I suggest you create a relationship and set up a both direction relationship so that you don't have to create MAXConfdate and QTYCONFIRMEDZE measures. Because two tables are equivalent to one table.

4.png


You only need to write a sum function to get the sum value, or max function to get the max value.

Sum = SUM('ZE'[SUMQTYZE])
Max = MAX('ZE'[SUMQTYZE])

5.png

 

 

Best Regards,
Stephen Tao

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

View solution in original post

1 REPLY 1
v-stephen-msft
Community Support
Community Support

Hi @LudivineLOU ,


I don't quite understand what value you want. May I ask how 550 is calculated? If you calculate the total, September should be 450.

1.png

 

QTY_conf = CALCULATE(SUM(ZE[SUMQTYZE]),FILTER(ZE,ZE[Pur. Doc. Itm]=MAX(GR[Pur. Doc. Itm])))

 

This measure you give is the max value of Purch item number. I filter the data for January as shown below, 800 is the maximum value, not the sum.

2.png

3.png

I found the PBI file from your other posts, and I found that there is no relationship between your two tables. I suggest you create a relationship and set up a both direction relationship so that you don't have to create MAXConfdate and QTYCONFIRMEDZE measures. Because two tables are equivalent to one table.

4.png


You only need to write a sum function to get the sum value, or max function to get the max value.

Sum = SUM('ZE'[SUMQTYZE])
Max = MAX('ZE'[SUMQTYZE])

5.png

 

 

Best Regards,
Stephen Tao

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors