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
powerbiasker
Helper IV
Helper IV

Is it possible to create a new Meausure based on the columns in a calculated table?

I have a calculated table (created by DAX): [date_dim].

 

I want to create a new Measure but get an error.

8.PNG

 

Is it possible to create a new Meausure based on the columns in a calculated table?

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

@powerbiasker 

 

The answer is yes, you can create measures based on calculated columns. As regards the measure you have posted, can you please post the measures you are using for [Month number] and [FY Month number]?

It would also help if you posted sample data, data structure and the outcome you are looking for





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

3 REPLIES 3
PaulDBrown
Community Champion
Community Champion

@powerbiasker 

 

The answer is yes, you can create measures based on calculated columns. As regards the measure you have posted, can you please post the measures you are using for [Month number] and [FY Month number]?

It would also help if you posted sample data, data structure and the outcome you are looking for





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown 

 

I use the following DAX to create the calculated table [date_dim].

 

date_dim = 
VAR basecalendar = CALENDAR(DATE(1990, 1, 1), DATE(2020, 12, 31))
return
generate (
basecalendar, 
var basedate = [Date]
var yeardate = year(basedate)
var monthnumber = month(basedate)
var yearmonthnumber = yeardate * 12 + monthnumber-1
var qtr = concatenate("Q",roundup(month([Date])/3,0))
var fy = IF(MONTH(basedate) <= 4, VALUE(FORMAT(basedate,"yyyy"))-1, VALUE(FORMAT(basedate,"yyyy")))
var fym = IF(MONTH(basedate) <= 4, MONTH(basedate)+8, MONTH(basedate)-4)
return row (
"Year", yeardate,
"Month Number", monthnumber, 
"Month", Format(basedate,"mmmm"),
"Year Month Number", yearmonthnumber,
"Year Month", Format(basedate, "mmm yyyy"),
"QTR", qtr, 
"Fiscal Year", CONCATENATE (fy, CONCATENATE("-",fy + 1)),
"FY Month Number", fym
)
)

 

 

I want to use ONE slicer for the month # for both Fiscal Year and Calendar Year.

https://community.powerbi.com/t5/Desktop/Can-I-use-1-slicer-for-the-month-for-both-Fiscal-Year-and/m... 

 

So, I try to create a new measure based on [Fiscal Year], [FY Month Number], [Year], [Month Number]. For example,

  • If I select a value in the slicer for [Fiscal Year], the slicer for the month # only works for measure [FY Month Number].
  • If I select a value in the slicer for [Year], the slicer for the month # only works for measure [Month Number].

 

But, as this question, I get an error...

 

 

@powerbiasker 

The issue you are facing (I suspect) is that you need to specify which month to return: measures can only return a single value; they cannot produce a list as a result. 

So the measure you are trying to write is saying that if you select [year], then return a list of months. This cannot be.

It has to return one single month and you need to tell the measure which value you want,

 

What you can do is:

Option 1:

1) create a slicer to select either Year or Fiscal Year.

2) The write measures which return calculations based on this selection. You can then use these measures in your visuals.

 

Option 2:

Use bookmarks to switch from year to fiscal year slicers, visuals etc.

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.