topic Re: Summarize table with another column of another table in DAX Commands and Tips

Summarize table with another column of another table

Anonymous — Wed, 05 Apr 2023 08:02:03 GMT

Hello,

I have this Dax formula :

This DAX formula make a group by column with two columne of my date table named "Date_IN_OUT" especially with "Semaine entreprise" column which is the Compagny Week which take value like "2023-01" and the two metric choose for this group by are a two sum based on different filter of my fact table "Data".

Now I want to group by on another column but this column isn't in my Date table but with my fact table and I don't how I can make that.
Note that my fact table and date table are link with ther column :

and this summarize table which I made are link with my fact table like this :

I hope someone can help me please.

Re: Summarize table with another column of another table

Greg_Deckler — Wed, 05 Apr 2023 08:40:48 GMT

Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Summarize table with another column of another table

Anonymous — Wed, 05 Apr 2023 09:37:49 GMT

Sorry for this forget, so currently I have this table after the DAX function :

Compagny

Week

Count

OUT

Year

progress

Compagny

Month

Number

2008-05

517

-4

2008

517

2008-M01

2008-06

-153

2008

151

2008-M02

And I want to have this table:

Compagny week	Count IN	Count OUT	Year	In Progress	Compagny month	Month number	Shop
2008-05	500	-2	2008	498	2008-M01	1	Mag1
2008-06	1	-100	2008	-99	2008-M01	1	Mag1
2008-05	17	-2	2008	-15	2008-M01	1	Mag2
2008-06	1	-53	2008	-52	2008-M01	1	Mag2

The problem is the Shop column is in my fact table "Data" and my summarize function is based on my date table.

Re: Summarize table with another column of another table

tamerj1 — Wed, 05 Apr 2023 10:08:57 GMT

Anonymous
You can summarize 'Data' instead of 'Date_In_Out'. That would give you access to the columns of both tables.

Re: Summarize table with another column of another table

Anonymous — Wed, 05 Apr 2023 12:39:50 GMT

Thanks for your response, it's work but partially the value for the result isn't good :

The number of IN are the same for each shop it's obviously a problem in my measure for calculate the number of IN and OUT but I cannot find it.

Re: Summarize table with another column of another table

tamerj1 — Wed, 05 Apr 2023 13:05:04 GMT

Anonymous

If you delete FILTER ( ALLSELECTED ( DATA ) ) part what do tou get?

Re: Summarize table with another column of another table

Anonymous — Wed, 05 Apr 2023 13:11:55 GMT

It's give me well number for the IN column but for the OUT column it's not good

Re: Summarize table with another column of another table

tamerj1 — Wed, 05 Apr 2023 14:15:51 GMT

Anonymous

For OUT add CROSSFILTER ( 'Table'[IN Date], 'Date'[Date], NONE )

Re: Summarize table with another column of another table

Anonymous — Wed, 05 Apr 2023 14:36:18 GMT

Where I add this functon in OUT column ?

Re: Summarize table with another column of another table

tamerj1 — Wed, 05 Apr 2023 15:31:51 GMT

Anonymous

No.
place it before FILTER as a seperate argument of CALCULATE

Re: Summarize table with another column of another table

Anonymous — Wed, 05 Apr 2023 15:39:18 GMT

It doesn't work

Re: Summarize table with another column of another table

tamerj1 — Thu, 06 Apr 2023 04:03:40 GMT

Anonymous
Please try

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

Re: Summarize table with another column of another table

Anonymous — Thu, 06 Apr 2023 06:53:45 GMT

It's not the well result.

May be we must place a REMOVEFILTER or KEEPFILTER function ?

Re: Summarize table with another column of another table

Anonymous — Wed, 12 Apr 2023 07:01:42 GMT

Hello,
Do you have a solution please ?