topic Re: Display distinct values with multiple OR filters in DAX Commands and Tips

Display distinct values with multiple OR filters

Anonymous — Tue, 10 Dec 2019 12:05:31 GMT

Hello!

I would like to consult to you this problem --- so I have the an ID, name, Col1, Col2, and Col3 columns..

I would like to display the distinct ID and name column into a table with (col1>=5 and col1<10) OR (col2>=5 and col2<15) OR (col3>=5 and col3<15) filters.

i created a calculated column like this, to serve as my filter..

ColumnFilter = SWITCH(TRUE(),
Query1[col1] >=5 && Query1[col1] < 10, ">=5 & <10" ,
Query1[col2] >=5 && Query1[col2] < 15, ">=5 & <15" ,
Query1[col3] >=5 && Query1[col3] < 15, ">=5 & <15"
)

but i am not sure if the SWITCH function functions like an OR operator.

I am also not sure how to apply this and get the distinct values in my ID column.

So basically i need to get the distinct ID values passed with any of the filters.

Thank you in advance!

Re: Display distinct values with multiple OR filters

az38 — Tue, 10 Dec 2019 17:11:57 GMT

Hi Anonymous

try to create newtable

= DISTINCT( SELECTCOLUMNS( FILTER( 'Query1'; OR( ([col1] >=5 && [col1]);OR(([col2] >=5 && [col2] );([col3] >=5 && [col3] )) ) ) ); "ID"; [ID] )

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Display distinct values with multiple OR filters

Anonymous — Mon, 16 Dec 2019 06:35:32 GMT

Hi az38,

not sure if i typed it correctly, and is it really new table? i used new column, how to create new table through this?

Re: Display distinct values with multiple OR filters

az38 — Mon, 16 Dec 2019 06:59:22 GMT

sorry, Anonymous my bad

try new table

= DISTINCT( SELECTCOLUMNS( FILTER( 'Query1';( ([col1] >=5 && [col1]) || ([col2] >=5 && [col2]) || ([col3] >=5 && [col3] ) ) ) ); "ID"; [ID] )

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Display distinct values with multiple OR filters

Anonymous — Mon, 16 Dec 2019 14:53:45 GMT

az38 ,

Thanks! i also created a new column with a formula also the same, same results as well but without the selectedcolumn function, i am going to use this. thanks! 🙂