topic Re: Pivot Columns with DAX in DAX Commands and Tips

Pivot Columns with DAX

GDUb — Mon, 21 Aug 2023 20:29:44 GMT

Greetings, Power BI pros. I'm trying to break a time series SPC chart into stages based on a list of stage start dates that will be added to over time. The end result I'm looking for looks like this:

The way I accomplished this in Excel was with a pivot table that has week starting on rows, and stage on columns, so each time series breaks into one column for each stage (in the case below, 3), like so:

I'd like to recreate this report using Power BI desktop, but I don't know how to break the single time series column into one column for each stage. Is there a DAX pivot pattern? I know that PowerQuery has pivot capabilities, but I am trying to avoid that. The key benefit of the pivot in Excel is that it updates properly when the user adds additional stages without any additional modification. Thanks!

Re: Pivot Columns with DAX

some_bih — Tue, 22 Aug 2023 06:01:16 GMT

Hi GDUb on the link below you can find list of Statistical function, go to single one and check example.

https://dax.guide/functions/statistical/

Did I answer your question? Kudos appreciated / accept solution!

Re: Pivot Columns with DAX

GDUb — Tue, 22 Aug 2023 14:05:30 GMT

Hi some_bih. Thanks for your reply, but I'm not sure I follow. How do statistical functions help me break single data columns into three separate columns by stage?

Re: Pivot Columns with DAX

some_bih — Tue, 22 Aug 2023 14:19:39 GMT

Hi GDUb on your picture I saw Mean

Re: Pivot Columns with DAX

GDUb — Tue, 22 Aug 2023 14:47:09 GMT

Thanks, @somebih. Sorry for the confusion. In a nutshell, what I'm trying to do is pivot a row to a column with DAX, turning something like this:

Planning Week	Stage	Sample Data
8/8/2022	1	0.01%
8/15/2022	1	0.00%
8/22/2022	1	0.29%
8/29/2022	1	0.20%
9/5/2022	1	-0.02%
9/12/2022	1	0.03%
9/19/2022	1	-0.05%
9/26/2022	1	-0.01%
10/3/2022	1	0.22%
10/10/2022	1	-0.03%
10/17/2022	1	0.00%
10/24/2022	1	-0.03%
10/31/2022	1	-0.34%
11/7/2022	2	0.09%
11/14/2022	2	-0.06%
11/21/2022	2	0.14%
11/28/2022	2	0.89%
12/5/2022	2	-0.16%
12/12/2022	2	0.41%
12/19/2022	2	-0.38%
12/26/2022	2	-0.01%
1/2/2023	2	0.32%
1/9/2023	3	0.14%
1/16/2023	3	-0.32%
1/23/2023	3	-0.02%
1/30/2023	3	-0.18%
2/6/2023	3	0.02%
2/13/2023	3	0.06%
2/20/2023	3	-0.00%
2/27/2023	3	-0.01%
3/6/2023	3	-0.01%

Into this:

	Stage
Planning Week	1	2	3
8/8/2022	0.01%
8/15/2022	0.00%
8/22/2022	0.29%
8/29/2022	0.20%
9/5/2022	-0.02%
9/12/2022	0.03%
9/19/2022	-0.05%
9/26/2022	-0.01%
10/3/2022	0.22%
10/10/2022	-0.03%
10/17/2022	0.00%
10/24/2022	-0.03%
10/31/2022	-0.34%
11/7/2022		0.09%
11/14/2022		-0.06%
11/21/2022		0.14%
11/28/2022		0.89%
12/5/2022		-0.16%
12/12/2022		0.41%
12/19/2022		-0.38%
12/26/2022		-0.01%
1/2/2023		0.32%
1/9/2023			0.14%
1/16/2023			-0.32%
1/23/2023			-0.02%
1/30/2023			-0.18%
2/6/2023			0.02%
2/13/2023			0.06%
2/20/2023			0.00%
2/27/2023			-0.01%
3/6/2023			-0.01%

I'm looking for a general solution where the number of stages is changing/unknown, so a brute force formula that explicitly names the stages, such as the one suggested at Solved: Re: Convert multiple column data into a column wit... - Microsoft Fabric Community, will not suffice.

Thanks!

Re: Pivot Columns with DAX

ERD — Tue, 22 Aug 2023 15:52:18 GMT

Hi GDUb , it's a bit diffent in PBI, but you can use a matrix and a line chart with small multiples:

Re: Pivot Columns with DAX

GDUb — Tue, 22 Aug 2023 18:16:22 GMT

Thanks, ERD. I'll experiment a bit and see what I can come up with.

Re: Pivot Columns with DAX

some_bih — Tue, 22 Aug 2023 18:33:41 GMT

Hi GDUb after I inserted data into Excel file, imported into pbi file, in Power Query I did transformation as on Pic1, then on tab Transform choose as on Pic 2 and final step is Pict 3.

Did I answer correctly? Kudos appreciate / accept solution.

Pic 1

Pict 2

Pic 3 final (you can replace and rename as you wish)

Re: Pivot Columns with DAX

GDUb — Wed, 23 Aug 2023 13:18:29 GMT

Hi, some_bih, I know that PowerQuery has pivot capability. I'm trying to avoid using that as I suspect it may break the fold. I'm looking for a DAX solution.