Forum Discussion

yigitarican's avatar
yigitarican
Frequent Visitor
2 years ago
Solved

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

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.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.





3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.





  • yigitarican's avatar
    yigitarican
    Frequent Visitor
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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.