Calculate with boolean filter and corresponding slicer not working

[Anonymous]

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

[Datatouille]

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]

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

[v-sihou-msft]

@Anonymous

In your .pbix, you put PostNr on rows in your matrix, it will slice all three measures. When you select Type 1 and apply "not 7000" report filter, it will filter out PostNr 8000 and 9000 only from the table. Then PostNr will slice all three measures including COUNTROWS for Type 2, so that you will get 4 on PostNr 8000 and BLANK() on PostNr 9000. This is expected behavior.

Regards,

[Datatouille]

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")

[Anonymous]

Thanks for the answer,

The idea is to get the total nr of A or B tasks within the context of the report filters, i.e. there are some filters in the page for example dates, task type, etc., and additionally there is this PostNr report filter that in my actual report removes lines with blank post code (could also be 7000). In my example the starting context consists of the 13 rows of A and B tasks belonging to post codes 6000, 8000, and 9000.

I just find this normal behaviour little bit difficult to get, that is we first apply the TaskType slicing filter (removing the postcode 6000 from the context because it does not have any TaskType A), and then we apply the CALCULATE TaskType filter, causing counting only 4 out of the 6 B tasks of the postcodes 6000, 8000, 9000 from my original context. Somehow I find the B tasks that belong to 9000 are more important because they happened to performed A tasks as well, where as those two B tasks that belong to 6000 do not get counted for because they did not perform any A tasks. The way I assumed it to work was that CALCULATE would simply ignore (overwrite) the TaskType slicer filter with its own filter argument, and therefore count all the 6 B tasks.

In addtion to your CALCULATE ( [Nr Of Tasks] ; Filter ( All (Tasks) ; Tasks[TaskType] = "A"), I think I would need to add Tasks[PostNr] <> 7000 to CALCULATE.

Best regards,

Kristjan

[Anonymous]

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.