cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate V
Advocate V

Use an X-measure for x-axis, instead of a calculated column?

Simplifed version of the situation, - say I have 2 views: 

 

  • CustomerDIM
  • TransactionFACT

Those go into a Data Model, which I'll connect to via Live Connection. When doing a live connection, one can't add calculated columns. 

I effectively need to add the calculated column that is in the below image, in blue, so that I can run numbers on that MonthsFromStateDate column.  My sweet blue arrow shows the columns that are used for the calculation. 

mmace1_1-1601236766093.png

 

 

I actually control the underlying views / data model, so: 

  • I could add it to the underlying View - but that's kind of a burdern on that View, just for this
  • I could add the column to the underlying data model <-- Current thinking, and the obvious choice to me. 
  • But what I wonder:  I could add a measure with row context (an 'X' measure). 
    • But then I couldnt' use that X measure as the axis in tables / graphs or anything, so that would kinda defeat the purpose.  Right...?
    • I could say, create a measure for the transactions that have a MonthsfromStateDate = 1, but then I'd have to make a 2nd measure for MonthsfromStartDate = 2, etc.  Kinda unwieldy. 

 

So question:  Calculated column in this case, or can you think of an equally good way to do this without creating a calculated column? 

 

1 ACCEPTED SOLUTION
Super User I
Super User I

@mmace1 

 

I think in your case, it's better you create that column in your underlying view, will be more efficient.

 

However there is always a way, you can think on below:

 

1). Lets assume you will have 20 unique 'MonthsfromStateDate' values, so create or have a separate table, having just one column 1 to 20.

2). Now you will create a measure, either you can use switch and define for each 'MonthsfromStateDate' or think about any other logic.

 

So in chart, now you can now drag the column from above point 1) table in the axis. 

 

Hope above is making sense. 

View solution in original post

3 REPLIES 3
Super User I
Super User I

@mmace1 

 

I think in your case, it's better you create that column in your underlying view, will be more efficient.

 

However there is always a way, you can think on below:

 

1). Lets assume you will have 20 unique 'MonthsfromStateDate' values, so create or have a separate table, having just one column 1 to 20.

2). Now you will create a measure, either you can use switch and define for each 'MonthsfromStateDate' or think about any other logic.

 

So in chart, now you can now drag the column from above point 1) table in the axis. 

 

Hope above is making sense. 

View solution in original post

@mhossain 

Thanks, If I created a 'MonthsFromStartDate' table - it wouldn't have any way to connect to the TransactionsFACT table, unelss I had a calculated column there.  So sorry I don't follow - how could I use that 'MonthsFromStartDate' table in the axis and have it work? 

Hi  @mmace1 ,

 

You can create a measure to connect the 2 tables,it would be easier to explain if you could upload your .pbix file to onedrive business and share the link with us?

Remember to remove the confidential information.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors