Forum Discussion

maryJ's avatar
maryJ
Frequent Visitor
7 years ago

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:


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:

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!
Need Help

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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.

     

     

    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")

    Best  Regards,

    Cherry

    • maryJ's avatar
      maryJ
      Frequent Visitor
      7 years ago

      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