cancel
Showing results 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.

Helper I

## problem with group /filter

Hello Everybody,

I have 2 tables :

Table ZE :

Table GR :

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 :

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

``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.

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.

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

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.

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.

``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.

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.

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

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.

Announcements

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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors