Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello, Here's my formula. It's telling me the Expression refers to multiple columns.
Count-Jobs (NEW) =
VAR SelectedYear =
SELECTEDVALUE ( CalendarTable[Year] )
RETURN
CALCULATE (
DISTINCTCOUNT ( Jobs[JobUnique] ),
FILTER (
FILTER ( ButaneSchedule, ButaneSchedule[Year] = SelectedYear ),
IF (
SELECTEDVALUE ( CalendarTable[Date] ) >= ButaneSchedule[Begin]
&& SELECTEDVALUE ( CalendarTable[Date] ) <= ButaneSchedule[End],
FILTER (
Jobs,
Jobs[Area] = SELECTEDVALUE ( Areas[Area] )
&& Jobs[JobAbbr] <> "V"
&& Jobs[JobAbbr] <> "V1"
&& Jobs[JobAbbr] <> "V2"
&& Jobs[JobAbbr] <> "V3"
&& Jobs[JobAbbr] <> "T"
&& Jobs[JobAbbr] <> "S"
&& Jobs[JobAbbr] <> "B"
&& Jobs[JobAbbr] <> "O"
&& Jobs[JobAbbr] <> "TA"
&& Jobs[JobAbbr] <> "SE"
&& Jobs[JobAbbr] <> "BT"
),
FILTER (
Jobs,
Jobs[Area] = SELECTEDVALUE ( Areas[Area] )
&& Jobs[JobAbbr] <> "V"
&& Jobs[JobAbbr] <> "V1"
&& Jobs[JobAbbr] <> "V2"
&& Jobs[JobAbbr] <> "V3"
&& Jobs[JobAbbr] <> "T"
&& Jobs[JobAbbr] <> "S"
&& Jobs[JobAbbr] <> "B"
&& Jobs[JobAbbr] <> "O"
&& Jobs[JobAbbr] <> "TA"
&& Jobs[JobAbbr] <> "SE"
)
)
)
)
Basically all I'm looking to do is "if the selected calendar date falls within the date range then distinct count based on *these* filters, otherwise, disctinctcount based on *those* filters
Do you have some sample data to tinker with?
FILTER returns a table, not a single column. Also the arguments after the first of a CALCULATE statement are already filters, so I'm not sure you need all of the FILTER statements.
Thanks for responding. I'm not exactly sure I can get you sample data. I'd basically have to dummify my whole report with all its data. I'll try to come up with another solution before attempting that, if possible. I guess I'm just missing the point a little though, I mean, I'm trying to return a DISTINCTCOUNT based on some filter criteria - that seems like it would be a normal thing to do doesn't it?
Have you tried it without all the explicit FILTER statements?
Because basically CALCULATE already works that way;
CALCULATE(Expression, Filter1, Filter2, etc.)
I believe this is a little bit cleaner, but I need to somehow incorporate a filter to ensure the ButaneSchedule table is filtered on the correct row, otherwise the code won't work.
FILTER ( ButaneSchedule, ButaneSchedule[Year] = SelectedYear ),
Count-Jobs (NEW) =
VAR SelectedYear =
SELECTEDVALUE ( CalendarTable[Year] )
RETURN
IF (
SELECTEDVALUE ( CalendarTable[Date] ) >= ButaneSchedule[Begin]
&& SELECTEDVALUE ( CalendarTable[Date] ) <= ButaneSchedule[End],
CALCULATE (
DISTINCTCOUNT ( Jobs[JobUnique] ),
FILTER (
Jobs,
Jobs[Area] = SELECTEDVALUE ( Areas[Area] )
&& Jobs[JobAbbr] <> "V"
&& Jobs[JobAbbr] <> "V1"
&& Jobs[JobAbbr] <> "V2"
&& Jobs[JobAbbr] <> "V3"
&& Jobs[JobAbbr] <> "T"
&& Jobs[JobAbbr] <> "S"
&& Jobs[JobAbbr] <> "B"
&& Jobs[JobAbbr] <> "O"
&& Jobs[JobAbbr] <> "TA"
&& Jobs[JobAbbr] <> "SE"
&& Jobs[JobAbbr] <> "BT"
)
),
CALCULATE (
DISTINCTCOUNT ( Jobs[JobUnique] ),
FILTER (
Jobs,
Jobs[Area] = SELECTEDVALUE ( Areas[Area] )
&& Jobs[JobAbbr] <> "V"
&& Jobs[JobAbbr] <> "V1"
&& Jobs[JobAbbr] <> "V2"
&& Jobs[JobAbbr] <> "V3"
&& Jobs[JobAbbr] <> "T"
&& Jobs[JobAbbr] <> "S"
&& Jobs[JobAbbr] <> "B"
&& Jobs[JobAbbr] <> "O"
&& Jobs[JobAbbr] <> "TA"
&& Jobs[JobAbbr] <> "SE"
)
)
)
But I don't think you need the FILTER statements at all, since they are already part of the CALCULATE. So after your IF, it should be simply
CALCULATE(COUNTROWS(Jobs[JobUnique]), Jobs[Area] = SELECTEDVALUE(Areas[Area]), Jobs[JobAbbr] <> "V", Jobs[JobAbbr] <> "V1", Jobs{JobAbbr) <> "V2" etc. etc.
User | Count |
---|---|
88 | |
85 | |
72 | |
66 | |
57 |
User | Count |
---|---|
129 | |
102 | |
91 | |
83 | |
67 |