Filter Only One Value In the X Axis Of Bar Chart By Using Slicer

[yigitarican]

Hello,

I have a table which consist of Task Name, Task Status and Task Completion Date columns. There are 4 statuses: Completed, Waiting, Postponed and (null) values. On the X-Axis I use "Task Status" as value while on the Y-Axis I use # of Tasks as value. Before adding slicer, visual looks like this:

It's not complicated visual but the problem starts when I add slicer. Since only tasks with "Completed" statuses have "Task Completion Date" in the datasource, other three status never appear on the visual if I use slicer and the visual looks like this:

To prevent that, I want slicer to apply filter only "Completed" status. That means only 1 bar in the graph will change if the user interact with slicer while 3 other remains the same. I tried several DAX to solve this issue but it always looks like the second image.

[yigitarican]

Hello @Anonymous,

Firstly, thank you for your help and your time. In slicer, I use "Task Completion Date" field as slicer value. Should I create seperate table for date? If so is there any way to update date table automatically as time moving foward? 

[Anonymous]

Hi @yigitarican 
Create seperate table for date is not required, I just chose to create it myself during the test, you can still choose to use "Task Completion Date" as slicer value.
And if you want to create a date table which is updated automatically as time moving foward, you can use the following DAX:

Date_slicer1 = CALENDAR(DATE(2023,1,1), TODAY())

'DATE(2023,1,1)' means that this date table is from 2023.1.1, and you can set this time by yourself, and 'TODAY()' means that the date table always ends today.

[Anonymous · Accepted solution]

Hi @yigitarican 

I'll start by showing you the test data I used and the date table that I used to create the slicer( from 2023.1.1 to 2023.5.31):

You can use the following DAX to create measures to calculate the number of tasks corresponding to Waiting, Postponed and (null) values:

Waiting = COUNTROWS(FILTER('Table','Table'[Task Status] = "Waiting"))

Postponed = COUNTROWS(FILTER('Table','Table'[Task Status] = "Postponed"))

Blank = COUNTROWS(FILTER('Table','Table'[Task Status] = "blank"))

For Completed Tasks, I use a separate DAX to count, which will apply the date interval in the slicer to dynamically calculate the number of Tasks:

Completed = 
CALCULATE(
    COUNT('Table'[Task Status]),
    FILTER(
        'Table',
        'Table'[Task Status] = "Completed" && 
        'Table'[Task Completion Date] >= MIN('Date_slicer'[Date]) &&
        'Table'[Task Completion Date] <= MAX('Date_slicer'[Date])
    )
)

Put 'Task Status' into the X axis and the 4 measures you just created into the Y axis:

And the final output is shown in the following figure:

Without slicer:

After applying slicer:

Best Regards,

Dino Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.