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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Convert Measures to a table

Hi there,

 

I have Job_Id and two Measures JobComplete (Precentages) and Margin (Also Presentages)

I would like to create a line chart with X-axes as Jobcomplete but you cant put measure as x-axes in chart

My question is how to create a (new) table based on these tree columns and my measures will be converted as a columns and I can use the line chart again (my meaures are very complex calculation) is there any way like summarize function to do the job? in another word how to convert measure value to a column  
Thanks,

Oded Dror 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous  ,

Here is how to create new table:
Go to MODELLING tab and select NEW TABLE.

 

The DAX to create new table is like below:

Table 2 = 
CALCULATETABLE( 
         SUMMARIZE( 'Table1'
                             ,'Table1'[Job_Id] 
                             , "JobComplete" , [JobComplete] 
                              , "Margin" , [Margin]
                              ) )

Let me know if that helps. If not please share your .pbix file
Cheers
Rob

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
hoang_vaeco
New Member

I don't know what is Table 1. If i have only two measure and want to combine in  a table to draw a bar chart, how can do ?

d_gosbell
Super User
Super User


@Anonymous wrote:

 

I would like to create a line chart with X-axes as Jobcomplete but you cant put measure as x-axes in chart

 


It's true that you can't put measures on the x-axis of a line chart. But if you want to plot two measures against each other a scatter chart might be a better option

 

If you had some simple data like the following:

 

Row Jobcomplete margin
1 50 20
2 50 30
3 60 20
4 60 30

 

A scatter chart would plot this as 4 discrete "dots" which I think is the better approach for plotting two measures against each other.

 

If you tried to draw a line chart from left to right any of the following representations would be valid depending on the order the rows were processed in. There are even more combinations if the line is allowed to double back on itself.

 

201908 scatter plot.png

Anonymous
Not applicable

Hi there,

 

Thank you for your response, I tired that but the scatter dosen't gives you line between the dots (like excel chart for example) and it very difficult to follow if you have few jobs to display - my wish list is to have a combination between line and scatter chart, but for now I use the other method that Rob suggested. - I need it one measure as X-axes and the oher as a line

 

 

Thanks,

Oded Dror

 

Anonymous
Not applicable

Hi @Anonymous  ,

Here is how to create new table:
Go to MODELLING tab and select NEW TABLE.

 

The DAX to create new table is like below:

Table 2 = 
CALCULATETABLE( 
         SUMMARIZE( 'Table1'
                             ,'Table1'[Job_Id] 
                             , "JobComplete" , [JobComplete] 
                              , "Margin" , [Margin]
                              ) )

Let me know if that helps. If not please share your .pbix file
Cheers
Rob

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Rob,

 

Thanks it works, one more question can you do Group By clause with this solution? (I didn't try)

 

Thanks

Oded Dror

Anonymous
Not applicable

Hi @Anonymous ,

From what I understand, by default, SUMMARIZE function cantains a built in Group By clause. For example, regarding the table you've just created, you will find only distinct value in your Job_ID column.

 

If you want to use a more explicit group by clause, try using GROUPBY function instead of SUMMARIZE.

Cheers,
Rob


Anonymous
Not applicable

Thanks Rob

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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