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
mmace1
Impactful Individual
Impactful Individual

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
mhossain
Solution Sage
Solution Sage

@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
mhossain
Solution Sage
Solution Sage

@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. 

mmace1
Impactful Individual
Impactful Individual

@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
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.