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'm trying to create a measure that ignores selections but still does some filters. I got the ALL-function to work, but I need to make two filters.
The code for the measure looks like this:
Study hours = CALCULATE(
SUM(view_Events[NrOfHours])
,ALL(Departments[Department])
,FILTER(Datetable, Datetable[TheDayOfYear] <= [DayOfTheYear])
,FILTER(view_Events,view_Events[EventYear] = SELECTEDVALUE(Datetable[TheYear]))
,view_Events[Status] IN {"Signed", "Started", "Finished"}
,view_Events[Code] IN {1, 3}
,view_Events[TypeID] IN {1})
)
So the part that isn't working is:
,FILTER(Datetable, Datetable[TheDayOfYear] <= [DayOfTheYear])
,FILTER(view_Events,view_Events[EventYear] = SELECTEDVALUE(Datetable[TheYear]))
I've searched on the forum but can't make any of the solutions work for me.
Does anybody have a solution or a workaround?
Thanks in advance.
Sincerely
Ash
Solved! Go to Solution.
Hi @Anonymous ,
Try this:
Study hours =
CALCULATE (
SUM ( view_Events[NrOfHours] ),
ALL ( Departments[Department] ),
FILTER ( ALL ( Datetable ), Datetable[TheDayOfYear] <= [DayOfTheYear] ),
FILTER (
ALL ( view_Events ),
view_Events[EventYear] = SELECTEDVALUE ( Datetable[TheYear] )
&& view_Events[Status]
IN { "Signed", "Started", "Finished" }
&& view_Events[Code]
IN { 1, 3 }
&& view_Events[TypeID] IN { 1 }
)
)
Or this:
Study hours 2 =
VAR Day_ =
[DayofTheYear]
VAR Year_ =
SELECTEDVALUE ( Datetable[TheYear] )
RETURN
CALCULATE (
SUM ( view_Events[NrOfHours] ),
ALL ( Departments[Department] ),
Datetable[TheDayOfYear] <= Day_,
view_Events[EventYear] = Year_,
view_Events[Status] IN { "Signed", "Started", "Finished" },
view_Events[Code] IN { 1, 3 },
view_Events[TypeID] IN { 1 }
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Try this:
Study hours =
CALCULATE (
SUM ( view_Events[NrOfHours] ),
ALL ( Departments[Department] ),
FILTER ( ALL ( Datetable ), Datetable[TheDayOfYear] <= [DayOfTheYear] ),
FILTER (
ALL ( view_Events ),
view_Events[EventYear] = SELECTEDVALUE ( Datetable[TheYear] )
&& view_Events[Status]
IN { "Signed", "Started", "Finished" }
&& view_Events[Code]
IN { 1, 3 }
&& view_Events[TypeID] IN { 1 }
)
)
Or this:
Study hours 2 =
VAR Day_ =
[DayofTheYear]
VAR Year_ =
SELECTEDVALUE ( Datetable[TheYear] )
RETURN
CALCULATE (
SUM ( view_Events[NrOfHours] ),
ALL ( Departments[Department] ),
Datetable[TheDayOfYear] <= Day_,
view_Events[EventYear] = Year_,
view_Events[Status] IN { "Signed", "Started", "Finished" },
view_Events[Code] IN { 1, 3 },
view_Events[TypeID] IN { 1 }
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Try like. What is DayOfTheYear a measure or a column
,FILTER(Datetable, Datetable[TheDayOfYear] <= [DayOfTheYear])
,FILTER(view_Events,view_Events[EventYear] = values(Datetable[TheYear]))
,filter(view_Events, view_Events[Status] IN {"Signed", "Started", "Finished"} && view_Events[Code] IN {1, 3} && view_Events[TypeID] IN {1})
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
I'm quite new to Power BI so I don't really know if there is an easy way to share sample file without sensitive data.
The suggested code neglected the ALL-function in the measure. So when a department was selected, it showed the study hours for just that selected department,.
Both DayOfTheYear and TheYear are columns (they are both from the Date table).
Hi @Anonymous ,
Can the expressions I provided above work in your scenario?
Best Regards,
Icey
Can you explain how it isn't working? What are you expecting and what are you getting?
Sorry, forgot to mention that part.
What I expect is getting the total of study hours for all departments even when a department is selected (the selection is supposed to affect other measures. Not this one).
The ALL-function is neglected when I have the two FILTER-conditions in my measure. So when a department is selected, it shows only the study hours for that department.
With the two FILTER-conditions removed, it shows the total of study hours (for all departments) even when I select a department. Just as I want it (but with the filters).
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |