Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
@Anonymous
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
Proud to be a Super User!
Paul on Linkedin.
@Anonymous
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
Proud to be a Super User!
Paul on Linkedin.
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.
So, I try to create a new measure based on [Fiscal Year], [FY Month Number], [Year], [Month Number]. For example,
But, as this question, I get an error...
@Anonymous
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.
Proud to be a Super User!
Paul on Linkedin.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
105 | |
88 | |
74 | |
67 |
User | Count |
---|---|
123 | |
112 | |
95 | |
83 | |
73 |