Remove blanks in the date column selected from field parameters

[crln-blue]

Hello!

I have a problem with my matrix table.. 

My goal is to create a matrix table, with category as the row and continuous dates as the columns, where the user can select the period of dates to be displayed.  I was able to do this (after 4 hrs lmao) using field parameters from the columns of my date table. This is also my first time in using field parameters and I do admit that I'm not sure if this is the best approach to my goal:

However, as you can see from the screenshot above, there are blanks in the column section. This is because I created columns in my date table to show the Current Month, Current Weak and Current Year. How can I remove the blank? If I exclude it, the slicer will no longer work.

Here's how I created the periods for my field parameters:

Here is my sample pbix: gdrive link

Thanks!

[danextian · Accepted solution]

Hi @crln-blue 

Both Current Mont and Current Week columns doesn't have values for all or rows in Dates Table. You can create a measure that uses the Field Parameter's Order column to switch between calculations while excluding blank category values.

Note: Referencing the other Parameter columns will cause an error.

Amount  - NonBlank Category Values = 
SWITCH (
    SELECTEDVALUE ( Period[Period Order] ),
    0,
        CALCULATE (
            SUM ( Sheet1[Amounts] ),
            KEEPFILTERS ( NOT ( ISBLANK ( 'Date Table'[Current Week] ) ) )
        ),
    1,
        CALCULATE (
            SUM ( Sheet1[Amounts] ),
            KEEPFILTERS ( NOT ( ISBLANK ( 'Date Table'[Current Month] ) ) )
        ),
    SUM ( Sheet1[Amounts] )
)

[Vijay_Chethan · Accepted solution]

Hello, here is the solution
i observed u already created table called slicer helper so i used it for solution:
Create a measure :

Measure=

SWITCH(

    SELECTEDVALUE('Slicer Helper'[Slicer Field]),

    "Current Week",

    CALCULATE(

        SUM(Sheet1[Amounts]),

        FILTER(

            'Date Table',

            WEEKNUM('Date Table'[Date], 2) = WEEKNUM(TODAY(), 2)

        )

    ),

    "Current Month",

    CALCULATE(

        SUM(Sheet1[Amounts]),

        FILTER(

            'Date Table',

            MONTH('Date Table'[Date]) = MONTH(TODAY()) && YEAR('Date Table'[Date]) = YEAR(TODAY())

        )

    ),

    "Current Year",

    CALCULATE(

        SUM(Sheet1[Amounts]),

        FILTER(

            'Date Table',

            YEAR('Date Table'[Date]) = YEAR(TODAY())

        )

    )

)

now use Rows : Category
Columns : Date(date table column)-> change formatting accordingly in general section
values : Measure

then for slicer use : slicer helper[slicer filed]


if this helps, please mark as solution

[crln-blue]

Thank you for this! Initially, the slicer helper is for the measure creation similar to yours. But I was focusing on removing the dates itself (and when I did, it removed the row data) and so I tried to do the field parameter approach.

Still, thank you for this! This approach works perfectly too!

[Vijay_Chethan]

here you worldnt need current month,week , year columns as well

[danextian · Accepted solution]

Hi @crln-blue 

Both Current Mont and Current Week columns doesn't have values for all or rows in Dates Table. You can create a measure that uses the Field Parameter's Order column to switch between calculations while excluding blank category values.

Note: Referencing the other Parameter columns will cause an error.

Amount  - NonBlank Category Values = 
SWITCH (
    SELECTEDVALUE ( Period[Period Order] ),
    0,
        CALCULATE (
            SUM ( Sheet1[Amounts] ),
            KEEPFILTERS ( NOT ( ISBLANK ( 'Date Table'[Current Week] ) ) )
        ),
    1,
        CALCULATE (
            SUM ( Sheet1[Amounts] ),
            KEEPFILTERS ( NOT ( ISBLANK ( 'Date Table'[Current Month] ) ) )
        ),
    SUM ( Sheet1[Amounts] )
)

[crln-blue]

Thank you! I applied this and didn't realize KEEPFILTERS is the syntax I should be using all along. Thank you again!

[johnt75]

Another approach is to create a new table which contains the period and the relevant dates. You can use

Date Period Slicer =
VAR CurrentYear =
    ADDCOLUMNS (
        CALENDAR (
            DATE ( YEAR ( TODAY () ), 1, 1 ),
            DATE ( YEAR ( TODAY () ), 12, 31 )
        ),
        "Period", "Current Year"
    )
VAR CurrentMonth =
    ADDCOLUMNS (
        CALENDAR (
            DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
            EOMONTH ( TODAY (), 0 )
        ),
        "Period", "Current Month"
    )
VAR StartWeek =
    TODAY () - WEEKDAY ( TODAY (), 1 ) + 1
VAR CurrentWeek =
    ADDCOLUMNS ( CALENDAR ( StartWeek, StartWeek + 6 ), "Period", "Current Week" )
VAR Result =
    UNION ( CurrentYear, CurrentMonth, CurrentWeek )
RETURN
    Result

Link this to your date table in a many-to-many single direction relationship so that 'Date Period Slicer' filters 'Date'.

Use columns from your date table in any visuals, but use the period column from the new table in the slicer.