topic Re: Filter Measure Results in DAX Commands and Tips

Filter Measure Results

josevaras — Sat, 01 Feb 2020 00:41:23 GMT

Hello,

I have the measure

DIVIDE([Total Sales LM] , [Sum Total Sales] ) -1

which returns, amongst others values, a -100. I would like to filter out the -100 out of the result set. I am using a Matrix that has as row values country names, values as total sales and on my column header, the measure above.

Thank you for your help.

Re: Filter Measure Results

kentyler — Sat, 01 Feb 2020 02:10:49 GMT

I assume you have this in some sort of table visual. Do you want to not show the -100 value, or not include the whole row where the divide returns -100 ?

Re: Filter Measure Results

josevaras — Sat, 01 Feb 2020 02:13:11 GMT

I would like the whole row to not show up

Re: Filter Measure Results

kentyler — Sat, 01 Feb 2020 02:15:58 GMT

Do you know what it is about that row that gives the minus result ? I don't think the DIVIDE function can make the row not show up. Perhaps if it returned a blank value that might cause the row not to be included. If so, you could wrap the DIVIDE in an IF() and return blank if the result was less than 0)

Re: Filter Measure Results

TheDataMustFlow — Sat, 01 Feb 2020 02:45:51 GMT

Hi josevaras ,

A possible approach would be to nest the DIVIDE within an IF:

= IF(

DIVIDE([Total Sales LM] , [Sum Total Sales] ) -1=-100,

BLANK(),

DIVIDE([Total Sales LM] , [Sum Total Sales] ) -1

)

Thatt should filter out the results where the DIVIDE([Total Sales LM] , [Sum Total Sales] ) -1 returns -100.