Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Calculate with boolean filter and corresponding slicer not working

Hi all,

 

UPDATE: I have moved this to Issues

 

I have three measures counting rows in table:

  • Nr Of Tasks = COUNTROWS(Tasks)
  • Nr Of A Tasks = CALCULATE([Nr Of Tasks]; Tasks[TaskType] = "A")
  • Nr Of B Tasks = CALCULATE([Nr Of Tasks]; Tasks[TaskType] = "B")

Then I have a slicer (filter) in the report with TaskType, I and get the following results when I use the slicer:

  1. TaskType nothing selected: Nr of Tasks = 14; A Tasks = 6; B Tasks = 8;
  2. TaskType A selected:           Nr of Tasks = 6;   A Tasks = 6; B Tasks = 7;
  3. TaskType B selected:           Nr of Tasks = 8;   A Tasks = 6; B Tasks = 8;

I do not understand why B tasks drops down like this, in my mind the filter argument in Calculate should overwrite the slicer, hence always return 8, or am I wrong?

 

Regards,

Kristjan

Need Help

6 Replies

  • Datatouille's avatar
    Datatouille
    Icon for Solution Sage rankSolution Sage
    9 years ago

    Yes, Indeed, it should !

    Can you share the model or show us what the table looks like ?

    All the elements belong to the same Table (Tasks), right ?

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks for your answer, all the elements came from the same table, but there was a Report Filter set on Post codes, that excluded rows with blank Post codes. The formula in this measure was wrong, and I have now fixed it.

       

      Thx.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Excelside,

       

      I was too fast to consider this as solved, I have re-created this error in a small power bi file and this is a onedrive link to the file

       

      https://1drv.ms/u/s!At5OeG4R7nfOhLByG0IU1__p5gVOYg

       

      The problem is when post codes are only associated with one type of task, i.e. either A or B then this happen.

       

      Best regards,

      Kristjan

      • Datatouille's avatar
        Datatouille
        Icon for Solution Sage rankSolution Sage
        9 years ago

        Hi Anonymous

         

        This behavior is normal.

         

        CALCULATE( [Nr Of Tasks] ; Tasks[TaskType] = "A") is the same as CALCULATE ( [Nr Of Tasks] ; Filter ( All (Tasks[TaskType]) ; Tasks[TaskType] = "A")

         

        As you are using another column than TaskType from 'Tasks' Table (PostNr) in the page/report filter, it decreases the number of rows available in the filter context.

        If you don't want any columns of 'Tasks' table to have an impact on your calculations, you should rewrite your measures like this:

         

        CALCULATE ( [Nr Of Tasks] ; Filter ( All (Tasks) ; Tasks[TaskType] = "A")