Forum Discussion

ihatesand's avatar
ihatesand
New Member
6 years ago
Solved

Ignore blank rows in Date Filter

I have a report that requires date filters.  The filter needs to be conditional, meaning if rows have certain values they should always be present no matter what is in the date filter.  I thought I h...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi ihatesand ,

     

    We can try to create a special calendar table as slicer to meet your requirement:

     

    SpecialCalendarTable = UNION(ADDCOLUMNS(CALENDARAUTO(),"LinkedDate",BLANK()),ADDCOLUMNS(CALENDARAUTO(),"LinkedDate",[Date]))

     

     

     


    If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared？


    By the way, PBIX file as attached.


    Best regards,

     

ihatesand's avatar
ihatesand
New Member
6 years ago

amitchandakthanks for the response. I am getting this error when attempting this: 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.'

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi ihatesand ,

 

We can try to create a special calendar table as slicer to meet your requirement:

 

SpecialCalendarTable = UNION(ADDCOLUMNS(CALENDARAUTO(),"LinkedDate",BLANK()),ADDCOLUMNS(CALENDARAUTO(),"LinkedDate",[Date]))

 

 

 


If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared？


By the way, PBIX file as attached.


Best regards,

 

  • ihatesand's avatar
    ihatesand
    New Member
    6 years ago

    Hi v-lid-msft ,

     

    This appears to solve my issue thank you.  The file you dropped helped tremendously, however Im not sure where you created the SpecialCalenderTable, it does not appear in your sources.

     

    Thanks

  • ihatesand's avatar
    ihatesand
    New Member
    6 years ago

    The issue Im running into with this solution is the following error: 'A single value for column 'StatusChangeDateTime' in table 'SCRA' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'.

     

    The code I used for new table is : '

    SpecialCalendarTable = UNION(ADDCOLUMNS(CALENDARAUTO()|"LinkedDate"|BLANK())|ADDCOLUMNS(CALENDARAUTO()|"LinkedDate"|'Table2'[Date]))

    '

     

    I used 'Table2' because I have multiple table

    • v-lid-msft's avatar
      v-lid-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi ihatesand ,

       

      The SpecialCalendarTable is a calculated table created in "Modelling"- "New Table", We do not need to add table name in the code, the [Date] Column is come from CalendarAuto(), which will generate a calendar table automatically from your other table.


      Best regards,