Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
CR
Resolver II
Resolver II

display a DAX table through a measure

Hi all,

I've a table1:

TypePhase1Phase2Phase3
Type112  
Type2 10 
Type3 814

and I would like to get the following table in a visual:

PhaseData
Phase112
Phase218
Phase314

To get this result, I get the following code:

 

Table2 = 
UNION(
    SUMMARIZE(Table1;"Phase";"Phase1";"Data";SUM(Table1[Phase1]));
    SUMMARIZE(Table1;"Phase";"Phase2";"Data";SUM(Table1[Phase2]));
    SUMMARIZE(Table1;"Phase";"Phase3";"Data";SUM(Table1[Phase3]))
)

 

 But I cannot create a new table in live connection so I assume it has to be stored in a measure.

 

Measure = 
var tableTemp = 
                UNION(
                    SUMMARIZE(Table1;"Phase";"Phase1";"Data";SUM(Table1[Phase1]));
                    SUMMARIZE(Table1;"Phase";"Phase2";"Data";SUM(Table1[Phase2]));
                    SUMMARIZE(Table1;"Phase";"Phase3";"Data";SUM(Table1[Phase3]))
                )
RETURN
...

 

The issue is I don't see how to proceed now with the RETURN...

Any idea ?

Regards,

CR

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Well, that's a pickle because normally you would do something like:

 

Measure = 
var tableTemp = 
                UNION(
                    SUMMARIZE(Table1;"Phase";"Phase1";"Data";SUM(Table1[Phase1]));
                    SUMMARIZE(Table1;"Phase";"Phase2";"Data";SUM(Table1[Phase2]));
                    SUMMARIZE(Table1;"Phase";"Phase3";"Data";SUM(Table1[Phase3]))
                )
RETURN
  SUMX(FILTER(tableTemp,[Phase] = MAX('Table'[Phase])),[Data])

 

But, this would require that you have those Phase1, Phase2, Phase3 for your visual.

 

I cannot think of a way around this. With a Live connection, you will have to adjust the model by either unpivoting those columns in the model (on the live data source you are connecting to) or create a disconnected table in the live data source you are connecting to.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

Well, that's a pickle because normally you would do something like:

 

Measure = 
var tableTemp = 
                UNION(
                    SUMMARIZE(Table1;"Phase";"Phase1";"Data";SUM(Table1[Phase1]));
                    SUMMARIZE(Table1;"Phase";"Phase2";"Data";SUM(Table1[Phase2]));
                    SUMMARIZE(Table1;"Phase";"Phase3";"Data";SUM(Table1[Phase3]))
                )
RETURN
  SUMX(FILTER(tableTemp,[Phase] = MAX('Table'[Phase])),[Data])

 

But, this would require that you have those Phase1, Phase2, Phase3 for your visual.

 

I cannot think of a way around this. With a Live connection, you will have to adjust the model by either unpivoting those columns in the model (on the live data source you are connecting to) or create a disconnected table in the live data source you are connecting to.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

hum hum... indeed, to be discussed with the guy in charge of the cube !

 

Thanks @Greg_Deckler 

amitchandak
Super User
Super User

Try Unpivot and then you should be able, to sum up

https://radacad.com/pivot-and-unpivot-with-power-bi

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.