Using ALL-function with FILTERS

[Anonymous]

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

[Icey · Accepted 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.

[amitchandak]

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

[Anonymous]

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

[Icey]

Hi @Anonymous ,

Can the expressions I provided above work in your scenario?

Best Regards,

Icey

[AlexisOlson]

Can you explain how it isn't working? What are you expecting and what are you getting?

[Anonymous]

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