Sum numeric values using filters

[lc1]

Hello,

I have two columns PAR_Hours which are numeric values and PAR_Codes which are text values. I need to sum all hours for specific codes (LN, LD and DR)

I tried using several versions of this formula to no avail, results are always blank.

CALCULATETABLE(VALUES(Absenteeism[PAR_HOURS]),filter(Absenteeism,Absenteeism[PAR_CODE]="LN"&"LD"&"DR"))

CALCULATE(SUM(Absenteeism[PAR_HOURS]),Absenteeism[PAR_CODE]="LN",Absenteeism[PAR_CODE]="LD",Absenteeism[PAR_CODE]="DR")

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=CALCULATE(SUM(Absenteeism[PAR_HOURS]),Absenteeism[PAR_CODE]="LN"||Absenteeism[PAR_CODE]="LD"||Absenteeism[PAR_CODE]="DR")

Hope this helps.

[lc1]

Thank you, it worked!

[Ashish_Mathur]

You are welcome.

[v-lionel-msft]

Hi @lc1 ,

Try this measure:

Measure=
CALCULATE(
    SUM(Absenteeism[PAR_HOURS]),
    FILTER(
        Absenteeism,
        Absenteeism[PAR_CODE] in {"LN", "LD", "DR"}
    )
)

OR

Measure1=
CALCULATE(
    SUM(Absenteeism[PAR_HOURS]),
    FILTER(
       ALL(Absenteeism),
        Absenteeism[PAR_CODE] in {"LN", "LD", "DR"}
    )
)

"MdxScript (Model 8, 37)...A table of multiple values was supplied where a single value was expected."

VALUES(Absenteeism[PAR_HOURS]) will return a single column table, however, you what you created is a measure, so the program reports an error.

VALUES() function 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Try

CALCULATETABLE(VALUES(Absenteeism[PAR_HOURS]),filter(Absenteeism,Absenteeism[PAR_CODE] in {"LN","LD","DR"}))
OR
CALCULATETABLE(VALUES(Absenteeism[PAR_HOURS]),filter(all(Absenteeism),Absenteeism[PAR_CODE] in {"LN","LD","DR"}))

[lc1]

It didn't work, got an error:

MdxScript (Model 8, 37)...A table of multiple values was supplied where a single value was expected.