cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

@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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors