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.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

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