topic Re: Summarize table in DAX Commands and Tips

Summarize table

prashantg364 — Thu, 22 Jun 2023 05:51:13 GMT

I need some help summarizing table
I have the below table as my input, for one cluster there will be either 2 or 3 or 4 or more entries, so based on Type & date I wish to summarize my table

Circle	Cluster	Type	Band	Date
A	A_001	POST	7	23-5-23
A	A_001	POST	3	4-6-23
A	A_001	PRE	7	13-4- 23
A	A_001	PRE	3	11-4-23
B	B_001	POST	7	23-5-23
B	B_001	PRE	7	13-4-23
B	B_001	PRE	3	11-4-23
C	C_001	POST	3	23-5-23
C	C_001	POST	7	4-6-23
C	C_001	PRE	3	13-4-23
C	C_001	PRE	7	11-4-23

The output which I am looking for is

Circle	Cluster	Type	Band	Date
A	A_001	POST	7	23-5-23
A	A_001	POST	3	4-6-23
B	B_001	POST	7	23-5-23
B	B_001	PRE	3	11-4-23
C	C_001	POST	3	23-5-23
C	C_001	POST	7	4-6-23

Can any one help.

Re: Summarize table

tamerj1 — Thu, 22 Jun 2023 07:53:21 GMT

Hi prashantg364
For B why did you consider 11-4-23 not 13-4-23?

Re: Summarize table

prashantg364 — Thu, 22 Jun 2023 08:12:46 GMT

For B & band 7 , i have both PRE & POST - so I want only POST

For B & band 3, I have only PRE - so I want only PRE

Re: Summarize table

Anonymous — Thu, 22 Jun 2023 09:00:25 GMT

Hi prashantg364 .

What you want to do is to summarize the table.

It also seems that you try to implement some sql-window logic to select POST if that is available.

I suggest u look at summarize table:

Data summarized = SUMMARIZE( Data, Data[Circle], Data[Cluster], Data[Type], "Dummy", DISTINCT(Data[Circle]) )

, and try to solve the problem using COALSCE and IF to select correct date values based on Type

Re: Summarize table

v-jianboli-msft — Mon, 03 Jul 2023 03:20:25 GMT

Hi prashantg364 ,

Please try:

Table 2 = SUMMARIZE('Table','Table'[Circle],'Table'[Cluster],'Table'[Band],"Type",CALCULATE(MAX('Table'[Type]),FILTER('Table',[Date]=MAX('Table'[Date]))),"Date",MAX('Table'[Date]))

Final output:

Best Regards,

Jianbo Li

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

Re: Summarize table

prashantg364 — Thu, 13 Jul 2023 16:37:38 GMT

Hi all,

I have a data set as shown in the input table,

Against each product name & product band, I have 2 KPI categories, 1. 3 KPI and 2. 5 KPI. In columns 3 & 4 I have total pass KPIs in each category. In the following few columns I have the details for failed KPIs in terms of 1 & 0. 1 means KPI is pass & 0 means KPI is fail.

Now I want to summarize my table where I can have only one row for each Product name & Product band combination, followed by pass KPI count in the next 2 columns for 2 different categories, and in the next 2 columns I want the failed KPI names separated by commas, as shown in output table.

Pls help, how I can do this either through power Query editor or by using some DAX.

Input table

Product name	Product band	KPI Category - 3 KPI(KPI1,KPI2,KPI3)	KPI Category - 5 KPI(KPI1,KPI2,KPI3,KPI4,KPI5)	KPI1	KPI2	KPI3	KPI4	KPI5
Car	7	3	5	1	1	1	1	1
Car	3	1	2	1	0	0	0	1
Bike	7	2	2	1	1	0	0	0
Bike	3	3	3	1	1	1	0	0
Cycle	7	3	4	1	1	1	1	0
Cycle	3	0	0	0	0	0	0	0

Ouput table

Product name	Product band	Total pass in 3 KPI	Total pass in 5 KPI	Failed KPI -KPI Category - 3 KPI(KPI1,KPI2,KPI3)	Failed KPI - KPI Category - 5 KPI(KPI1,KPI2,KPI3,KPI4,KPI5)
Car	7	3	5
Car	3	1	2	KPI2,KPI3	KPI2, KPI3, KPI4
Bike	7	2	2	KPI3	KPI3, KPI4, KPI5
Bike	3	3	3		KPI4, KPI5
Cycle	7	3	4		KPI5
Cycle	3	0	0	KPI1,KPI2,KPI3	KPI1,KPI2,KPI3,KPI4,KPI5

Re: Summarize table

v-jianboli-msft — Fri, 14 Jul 2023 05:55:16 GMT

Hi prashantg364 ,

Please try:

First Unpivot columns:

Then add two new columns:

Failed KPI -KPI Category - 3 = Text.Combine( Table.SelectRows(#"Unpivoted Columns", (x)=>x[Product name]=[Product name] and x[Product band] = [Product band] and (x[Attribute] ="KPI1" or x[Attribute] ="KPI2" or x[Attribute] ="KPI3") and x[Value]=0)[Attribute],",") Failed KPI - KPI Category - 5 = Text.Combine( Table.SelectRows(#"Unpivoted Columns", (x)=>x[Product name]=[Product name] and x[Product band] = [Product band] and x[Value]=0)[Attribute],",")

Then Remove duplicate rows

Final output:

Here is the M code:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUtJRMgdiYyA2BWJDDByrA1NnDBUzgtIGSBiizikzOxVqoBGSQlTFSAqNkbAhhmKwzZXJOalIbjTB4kZUlcZoLkPFsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product name" = _t, #"Product band" = _t, #"KPI Category - 3 KPI(KPI1,KPI2,KPI3)" = _t, #"KPI Category - 5 KPI(KPI1,KPI2,KPI3,KPI4,KPI5)" = _t, KPI1 = _t, KPI2 = _t, KPI3 = _t, KPI4 = _t, KPI5 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product name", type text}, {"Product band", Int64.Type}, {"KPI Category - 3 KPI(KPI1,KPI2,KPI3)", Int64.Type}, {"KPI Category - 5 KPI(KPI1,KPI2,KPI3,KPI4,KPI5)", Int64.Type}, {"KPI1", Int64.Type}, {"KPI2", Int64.Type}, {"KPI3", Int64.Type}, {"KPI4", Int64.Type}, {"KPI5", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product name", "Product band", "KPI Category - 3 KPI(KPI1,KPI2,KPI3)", "KPI Category - 5 KPI(KPI1,KPI2,KPI3,KPI4,KPI5)"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Failed KPI -KPI Category - 3", each Text.Combine( Table.SelectRows(#"Unpivoted Columns", (x)=>x[Product name]=[Product name] and x[Product band] = [Product band] and (x[Attribute] ="KPI1" or x[Attribute] ="KPI2" or x[Attribute] ="KPI3") and x[Value]=0)[Attribute],",")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Failed KPI - KPI Category - 5", each Text.Combine( Table.SelectRows(#"Unpivoted Columns", (x)=>x[Product name]=[Product name] and x[Product band] = [Product band] and x[Value]=0)[Attribute],",")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute", "Value"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Product name", "Product band", "KPI Category - 3 KPI(KPI1,KPI2,KPI3)"}) in #"Removed Duplicates"

Best Regards,

Jianbo Li

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