topic Re: Problem with my summarize table when I filter on category in DAX Commands and Tips

Problem with my summarize table when I filter on category

Anonymous — Thu, 13 Apr 2023 11:04:57 GMT

Hello,

So I have this Summarize table that show the number of IN and OUT per week :

Week	IN	OUT
2008-05	517	-4
2008-06	2	-153
2008-07	4	-107

This table is create with this measure :

Entrée/Sortie = VAR lastDataWeek = MAX ( Data[#Semaine de mesure] ) RETURN SUMMARIZE ( ALL('Date_IN_OUT 1'), 'Date_IN_OUT 1'[Semaine Entreprise], "Entrée", CALCULATE(SUM(Data[#Nombre entrée]), FILTER(ALLSELECTED(Data), Data[#Semaine entrée entreprise] = 'Date_IN_OUT 1'[Semaine Entreprise] && Data[#Semaine de mesure]==lastDataWeek)), "Sortie",CALCULATE(SUM(Data[#Nombre sortie]), FILTER(ALLSELECTED(Data),Data[#Semaine sortie entreprise] = 'Date_IN_OUT 1'[Semaine Entreprise] && Data[#Semaine de mesure] == lastDataWeek)) )

And it's work well until I want to make a filter on a shop that's the result :

Week	Shop	Entrée	Sortie
2008-05	Mag 1	517	-4
2008-05	Mag 2	517	-4
2008-05	Mag 3	517	-4
2008-05	Mag 4	517	-4
2008-05	Mag 5	517	-4
2008-05	Mag 6	517	-4

That's the mesure :

Entrée/Sortie = VAR lastDataWeek = MAX ( Data[#Semaine de mesure] ) RETURN SUMMARIZE ( ALL('Data'), 'Date_IN_OUT 1'[Semaine Entreprise], Data[#Nom complet magasin], "Entrée", CALCULATE(SUM(Data[#Nombre entrée]), FILTER(ALLSELECTED(Data), Data[#Semaine entrée entreprise] = 'Date_IN_OUT 1'[Semaine Entreprise] && Data[#Semaine de mesure]==lastDataWeek)), "Sortie",CALCULATE(SUM(Data[#Nombre sortie]), FILTER(ALLSELECTED(Data),Data[#Semaine sortie entreprise] = 'Date_IN_OUT 1'[Semaine Entreprise] && Data[#Semaine de mesure] == lastDataWeek)) )

My fact table is "Data" and my date Table is "Date_IN_OUT".
It's been a long time that I'm blocked on this someone can help me please.

Re: Problem with my summarize table when I filter on category

johnt75 — Thu, 13 Apr 2023 14:55:52 GMT

Try

Entrée/Sortie = VAR lastDataWeek = MAX ( Data[#Semaine de mesure] ) RETURN ADDCOLUMNS ( SUMMARIZE ( 'Data', 'Date_IN_OUT 1'[Semaine Entreprise], Data[#Nom complet magasin] ), "Entrée", VAR Entreprise = 'Date_IN_OUT 1'[Semaine Entreprise] RETURN CALCULATE ( SUM ( Data[#Nombre entrée] ), Data[#Semaine entrée entreprise] = Entreprise && Data[#Semaine de mesure] == lastDataWeek ), "Sortie", VAR Entreprise = 'Date_IN_OUT 1'[Semaine Entreprise] RETURN CALCULATE ( SUM ( Data[#Nombre sortie] ), Data[#Semaine sortie entreprise] = Entreprise && Data[#Semaine de mesure] == lastDataWeek ) )

Re: Problem with my summarize table when I filter on category

Anonymous — Thu, 13 Apr 2023 15:11:53 GMT

Thanks for your response.
It's work for IN column but not for OUT column:

Re: Problem with my summarize table when I filter on category

johnt75 — Thu, 13 Apr 2023 15:19:59 GMT

Are you sure that there is OUT data for the other shops? The -4 which it put into the first shop matches the figure for the total number of outs which was produced by your previous code

Re: Problem with my summarize table when I filter on category

Anonymous — Thu, 13 Apr 2023 15:26:26 GMT

For this case yes but for the week 6 of year 2008 this is not the good result we have 0 out :

Whereas in other data without filter they have -153 OUT :

Re: Problem with my summarize table when I filter on category

johnt75 — Thu, 13 Apr 2023 15:36:29 GMT

Again, that could be split across different shops. I don't see any differences between the code for the ins and for the outs, other than filtering on the in and out column from the Data table.

I would check the source data to see which shops had the outs in a given week and then compare that to the summarized table.

Re: Problem with my summarize table when I filter on category

Imad-R — Thu, 13 Apr 2023 17:46:35 GMT

I couldn't send the data.

Re: Problem with my summarize table when I filter on category

tamerj1 — Thu, 13 Apr 2023 21:50:19 GMT

Hi Anonymous

please try

Entrée/Sortie =
VAR T =
ALL ( 'Data' )
RETURN
GENERATE (
SUMMARIZE ( T, 'Date_IN_OUT 1'[Semaine Entreprise], Data[#Nom complet magasin] ),
VAR lastDataWeek =
CALCULATE (
MAX ( Data[#Semaine de mesure] ),
ALL ( Data[#Nom complet magasin] )
)
VAR Entree =
CALCULATE (
SUM ( Data[#Nombre entrée] ),
FILTER (
T,
Data[#Semaine entree entreprise] = 'Date_IN_OUT 1'[Semaine Entreprise]
&& Data[#Semaine de mesure] == lastDataWeek
)
)
VAR Sortie =
CALCULATE (
SUM ( Data[#Nombre sortie] ),
FILTER (
T,
Data[#Semaine sortie entreprise] = 'Date_IN_OUT 1'[Semaine Entreprise]
&& Data[#Semaine de mesure] == lastDataWeek
)
)
RETURN
ROW ( "Entrée", Entree, "Sortie", Sortie )
)

Re: Problem with my summarize table when I filter on category

Anonymous — Thu, 20 Apr 2023 07:58:41 GMT

Hello,
For it to work well, il's necessary that it's appear -153 for 2008-06 week because this week is represented by one shop and this isn't the case.