cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
maryJ Frequent Visitor
Frequent Visitor

Filter on column by ranges

Hi all,
I believe it is simple but I need your help as I am stucked.
Here is what i have:
testtable.JPG

Underreported hours are marked with red and are negative values. Over/Underreported column is a sum by month.
What I want to do is to create a filter for Over/Underreported column:
filter.JPG

I am using the following calculated column for my filter
Filter = 
VAR gap =
CALCULATE(ABS(SUM('Table1'[Over/Underreported Time]))
RETURN
SWITCH(TRUE(), gap>=8 && gap<16, ">8h",
gap>=16 && gap<=40,">16h",
gap>40 ,">40h",
"less than 8")

But it doesn't work as expected. For now, I am using an Import mode, but I need the final solution for Direct Query mode. So my  calculation mentioned above won't also work in direct query.
Please let me know if any other information required.
Could you please help me on this? 
Thank you in advance!
2 REPLIES 2
Community Support Team
Community Support Team

Re: Filter on column by ranges

Hi @maryJ ,

 

 By my tests and research, I'm afraid that your  calculated column formula will not work in Direct Query Mode.

 

You'd better create the measure with that formula in direct query mode, it should be work by my test.

 

Capture.PNG

 

You could have  a good look at these limitations before you use Direct Query.

 

I' m afraid that you may try to create a custom column in Query Editor. Please refer to the formula below.

 

= Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom]>8 and [Custom]<16 then ">8" else if [Custom]>16 then ">16" else "less than 8")

custom column.PNG

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
maryJ Frequent Visitor
Frequent Visitor

Re: Filter on column by ranges

Hi @v-piga-msft ,
Thank you very much for your response.
I appreciate your help.
Unfortunately, it doesn't work for me as my data for Over/Under Reported time column is presented as a daily period (probably I forgot to mention that). And then I use a measure to sum Over/Under Reported time by month using this formula:

PROJECT_GAP = CALCULATE(SUMX('Table1',"Table1'[PROJECT_GAP]),ALL('Table1'[CALENDAR_DATE]))


Anyway, in order to create a slicer I need a column and it is also impossible to create a column in direct quiery based on this measure.
I also tried to create a custom column in Query Editor by your example but due to those limitations, my formula doesn't work .

Thank you,
Best Regards
Mary 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors