cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jaromac
Frequent Visitor

Dax: Row to Column Transposition

Hi PowerBI users,

 

I would like to find out whether is possible to transpose rows into columns against Phases field. I have already prepared a intial logic however I still missing to populate the rest of the records which basically they do not meet the conditions : 

Jaromac_0-1647276647436.png

So in the end I would like repeat blank values with the same value even though it is not a correnspoding phase. The logic for Phase 1 for example is : 

Phase 1 =

var Phase_1_value = if(Aggregate_view[Phases]="Phase 1",(Aggregate_view[Phase_done_progress]),BLANK())

return
Phase_1_value
 
Is there any way instead of having BLANK put the same result according to the PHASE ? 
 
P.S. I know that there is a default transpose function in query and I am enforced to do in DAX. 
 
Your help would highly appreciated. 
 
Thanks in advance. 
1 ACCEPTED SOLUTION

Hi, @Jaromac 

You don't need to use 'if' statement at all. To replace the blank, you just need to change the code as below:

Calculated columns:

Phase_1 = 
CALCULATE (
    MAX ( Aggregate_view[Phase_done_progress] ),
    FILTER ( Aggregate_view, Aggregate_view[Phases] = "Phase 1" )
)
Phase_2 = 
CALCULATE (
    MAX ( Aggregate_view[Phase_done_progress] ),
    FILTER ( Aggregate_view, Aggregate_view[Phases] = "Phase 2" )
)
Phase_3 = 
CALCULATE (
    MAX ( Aggregate_view[Phase_done_progress] ),
    FILTER ( Aggregate_view, Aggregate_view[Phases] = "Phase 3" )
)

10.png

Best Regards,
Community Support Team _ Eason

View solution in original post

3 REPLIES 3
VahidDM
Super User
Super User

@Jaromac 

 

Are those "Phase_done_progress" numbers always the same for each phase?

 

For instance, the Phase 2 is always 97%?


Appreciate your Kudos!!
LinkedIn: 
www.linkedin.com/in/vahid-dm/

 

 

 

Yes 🙂 In the end I have a another column which reduce those numbers of records to have only one value but firstly I need to populate all blank records. 

Hi, @Jaromac 

You don't need to use 'if' statement at all. To replace the blank, you just need to change the code as below:

Calculated columns:

Phase_1 = 
CALCULATE (
    MAX ( Aggregate_view[Phase_done_progress] ),
    FILTER ( Aggregate_view, Aggregate_view[Phases] = "Phase 1" )
)
Phase_2 = 
CALCULATE (
    MAX ( Aggregate_view[Phase_done_progress] ),
    FILTER ( Aggregate_view, Aggregate_view[Phases] = "Phase 2" )
)
Phase_3 = 
CALCULATE (
    MAX ( Aggregate_view[Phase_done_progress] ),
    FILTER ( Aggregate_view, Aggregate_view[Phases] = "Phase 3" )
)

10.png

Best Regards,
Community Support Team _ Eason

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.