cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## weird behavior with filtering

Below are the screen shot of two tables in Power Bi. One without any filter and the other is filter with Average as 0 and what I get is count as 10 , while ideally it should be 1 as you can see in above table, there is only one row where average is zero.

Why this weird behaviour??

If I add ExternalId column in 2nd table I get count as 1 and when I removed it I get count as 10?? Can someone please explain me what wrong am i doing here??

Filter of 2nd Table

1 ACCEPTED SOLUTION
Super User

Hi @PowerrrBrrr ,

This is not a weird behaviour this is related with the context of the calculation.

When using a table to summarize the values, the more details you add the more specific the calculation gets so in this case when you look at the first table at a row level you get the average value for each of the lines and you have a line with 0. The same thing for the count of ID in a detailed view you get 1 for each row.

If you look at the total line of the first table you will see that the average is 1.850.630,69.

When you remove the other columns except for the average and the ID count the context is at the table level so the average value is the same (1.850.630,69) and the count of ID is also 10, even if you do the filter of the average that will not work because in this case the average is being calculated for the group values so you are comparing the 1.850.630,69 to 0 and not removing the 0 averages for details.

In this case you need to do a different calculation for the ID count adding  a measure that makes the row context the same at the level you need.

Try the following measure:

``````IDCOUNT =
COUNTROWS (
FILTER (
SUMMARIZE (
Table,
Table[ExternalID],
Table[TimeStamp],
"AVERAGEVALUE", AVERAGE ( Table[Columntobeaveraged] )
),
[AVERAGEVALUE] = 0
)
)``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Super User

Hi @PowerrrBrrr ,

This is not a weird behaviour this is related with the context of the calculation.

When using a table to summarize the values, the more details you add the more specific the calculation gets so in this case when you look at the first table at a row level you get the average value for each of the lines and you have a line with 0. The same thing for the count of ID in a detailed view you get 1 for each row.

If you look at the total line of the first table you will see that the average is 1.850.630,69.

When you remove the other columns except for the average and the ID count the context is at the table level so the average value is the same (1.850.630,69) and the count of ID is also 10, even if you do the filter of the average that will not work because in this case the average is being calculated for the group values so you are comparing the 1.850.630,69 to 0 and not removing the 0 averages for details.

In this case you need to do a different calculation for the ID count adding  a measure that makes the row context the same at the level you need.

Try the following measure:

``````IDCOUNT =
COUNTROWS (
FILTER (
SUMMARIZE (
Table,
Table[ExternalID],
Table[TimeStamp],
"AVERAGEVALUE", AVERAGE ( Table[Columntobeaveraged] )
),
[AVERAGEVALUE] = 0
)
)``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors