[DAX] how to change row context to column

[fishleaf]

Hello,

I have the following data and a customized measure (whole number). The table looks like this. (I only have 3 types: A, B,C)

Project	Type	Measure
P1	A	1
P1	B	2
P1	C	0
P2	A	2
P2	B	0
P3	B	1

I want to use DAX to manually pivot the table, which means I need to change the context to the measure.

This is the result I want

Project	A	B	C
P1	1	2	0
P2	2	0
P3		1

Here is my original measure:

Measure = (IF…)

(I skip this because it is unrelated)

(If you are interested, the actual Measure = IF(AND(MAXA(v_DTD_Staffing[POSITION_STATUS]) <3,COUNTA(v_DTD_Staffing[POSITION_STATUS]) =1),0,IF(AND(MAXA(v_DTD_Staffing[POSITION_STATUS]) =4,COUNTA(v_DTD_Staffing[POSITION_STATUS]) =1),2,1)) 

)

The new measure I created is:

A = MAXX(FILTER(‘table’,’table[type]=”A”), (IF…))

However, this result is different from I expected. I tried AVERAGEX, MAXX, MINX. None of them got what I expected

I can’t use CALCULATE because I am doing this on a measure.

Any suggestions on how to make the context change – or more specificly, how to use DAX to convert row context to column context?

Thank you!!!

Matthew

[Vvelarde · Accepted solution]

@fishleaf

Try this:

Newtable = SUMMARIZE(ProjectsABC;ProjectsABC[Project];"A";CALCULATE(SUM(ProjectsABC[Measure]);ProjectsABC[Type]="A");"B";CALCULATE(SUM(ProjectsABC[Measure]);ProjectsABC[Type]="B");"C";CALCULATE(SUM(ProjectsABC[Measure]);ProjectsABC[Type]="C")).

Let me know if this help you

[fishleaf]

@Vvelarde Thanks a lot for the reply. I didn't use SUMMARIZE before, so I'm just wondering how to use that.

Should I create a new column, and VAR Newtable = SUMMARIZE() RETURN? 

Thank you!! 

[Vvelarde]

@fishleaf

Go to Modeling -New Table

And Copy & Paste to sentence that i wrote in previous post and press Enter.

This action create a new table that you can see and use like any other table.

Create a visualization (like a tab) and drag the fields (project, A, B and C) from this new table and this show you the results that you expected.

[fishleaf]

@Vvelarde Thanks a lot for your quick reply!

However, I run into the same issue that I can't SUM my measure. I checked and saw it is a Whole Number type, but the system doesn't pass the measure to SUM -- I saw in your screenshot you could do it. How did you make that happen?

Thank you!

 

[Vvelarde]

@fishleaf

Try this:

Don't use sum:

put this Calculate(measure.....

[fishleaf]

@Vvelarde

Thanks a lot!! This time it works perfectly! 

Now I know my issue is that I don't know I can use a measure in CALCULATE without aggregation functions. With your suggestion, I'm able to perform the context transform! 

Thanks a lot for your wisdom and time! Also thanks a lot for your quick reply!!

Matthew