Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Rob,

       

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

       

      Thanks

      Oded Dror

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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


  • d_gosbell's avatar
    d_gosbell
    Super User
    7 years ago
    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

       

  • hoang_vaeco's avatar
    hoang_vaeco
    Regular Visitor
    2 years ago

    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 ?