Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Display distinct values with multiple OR filters

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!

1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

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


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

View solution in original post

4 REPLIES 4
az38
Community Champion
Community Champion

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

LinkedIn


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

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?

 

 

zcolumn2.png

az38
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

@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! 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors