topic Re: Issues using FILTER in combination with SUM in DAX Commands and Tips

Issues using FILTER in combination with SUM

Anonymous — Tue, 25 May 2021 09:33:41 GMT

I am trying to make a custom measure. What I want is to:

Filter out some rows from an existing table.
Then from the filtered table select a certain column.
Then square all values in the column.
Then sum all values in the column.

I thought I could use a FILTER to do this but that won't work. I am getting an error telling me the syntax for the SUM function is incorrect (have not even squared the number yet).

SUM(FILTER(MyTable, MyTable[class] = "1")[columnToSelect])

Re: Issues using FILTER in combination with SUM

HashamNiaz — Tue, 25 May 2021 09:52:35 GMT

Hi Anonymous !

You can use following DAX coede;

Class = CALCULATE(SUM(Table[Column]), FILTER(Table, Table[Class] = "1"))

Table is your table name & column is your column you want to sum.

Regards,

Hasham

Re: Issues using FILTER in combination with SUM

Anonymous — Tue, 25 May 2021 10:03:14 GMT

How would one square all values before summing them?

Re: Issues using FILTER in combination with SUM

HashamNiaz — Tue, 25 May 2021 10:13:04 GMT

Hi Anonymous !

Create a new column using following DAX to have Sqr. values of your column;

SqrtColumn = SQRT(Table[Column])

Now you can use this new column in your DAX measure formula;

Class = CALCULATE(SUM(Table[SqrtColumn]), FILTER(Table, Table[Class] = "1"))

Regards,

Hasham