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

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,