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

[powerbiasker]

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

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

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

[PaulDBrown · Accepted solution]

@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

[powerbiasker]

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

[PaulDBrown]

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