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.
Hi,
I was using Power-BI Desktop (April 2018) to generate some DAX code via 'Quick Measure'
this was then to be included back in my SSAS tabular model (SSDT 17.4 build 14.0.61712.050)
TerminatedFTE := CALCULATE (
sum('Turnover'[End Period Terminated FTE]),
DATESBETWEEN(
'Date'[Date].[Date],
STARTOFMONTH(DATEADD(ENDOFMONTH('Date'[Date].[Date]), -11, MONTH)),
ENDOFMONTH('Date'[Date].[Date])
)
)
However an error is generated in VS:-
"Warning Measure 'Turnover'[TerminatedFTE] : Qualified name 'Date[Date].[Date]' is not allowed in this context"
I can find no reference to this style of extra qualification including other generated examples such as
'Date'[Date].[Year],
'Date'[Date].[QuarterNo],
'Date'[Date].[Quarter],
What's going on? Are there multiple dialects of DAX out there.
Thanks for any insight
JC
Hi,
Not sure of what you want but try this measure
=CALCULATE(sum('Turnover'[End Period Terminated FTE]),DATESBETWEEN('Date'[Date],EDATE(MIN('Date'[Date]),-11),MAX('Date[Date])))
In the visual, drag the date/month/year from the Date Table.
Hope this helps.
Thanks but I want to know what the triple qualification like 'Date'[Date].[Date] is based on and where it is documented.
And secondly why it only seems acceptable in Power-bi desktop
Hi,
I do not know. I have never used that form. Is my formula working?
Yes that formula works or at lease gives the same answer as my alternative
TerminatedFTE:= CALCULATE (
sum('Turnover'[End Period Terminated FTE]),
DATESBETWEEN(
'Date'[Date],
STARTOFMONTH(DATEADD(ENDOFMONTH('Date'[Date]), -11, MONTH)),
ENDOFMONTH('Date'[Date])
)
)
which just stripped out the 3rd level qualification.
Going via SSAS also seems to have lost the auto date intelligence.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |