Forum Discussion

Fanis_Georg's avatar
Fanis_Georg
Regular Visitor
1 year ago
Solved

Count values in Tabel Visual

Hello Team

 

I have a table visual in my report, when I apply the filter I see only 2 rows but the count at the bottom of the table shows like that, in this particular case 420. Do you know why this is happening? I would expect result 2, and every time i change the the filter the table to react in a similar way as when we use the sub functions in excel and only counts the filtered values.

Thank you

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from Fowmy , please allow me to provide another insight:

    Hi, Fanis_Georg 
    Thanks for reaching out to the Microsoft fabric community forum.

    The issue you're encountering is commonly due to using measures as visual-level filters. This causes the COUNT aggregation method's calculation process to not be directly affected by the visual-level filters. In other words, when you use an aggregation method on a field, using measures as visual-level filters will only determine whether the row values are displayed, but will not affect the calculation result itself.

     

    I recommend using the FILTER() function to include your filter conditions and then writing a measure to replace the implicit aggregation on the visual object.

     

    Here is a link to a similar issue:

    Solved: Re: Visualization data changes when using count fi... - Microsoft Fabric Community

     

    Additionally, you can use the ISINSCOPE() function to specifically modify the total section.

    For more details, please refer to the documentation:

    ISINSCOPE function (DAX) - DAX | Microsoft Learn
    HASONEVALUE function (DAX) - DAX | Microsoft Learn
    Hierarchies – DAX Patterns
     

    Here is my test process:

    1.First, this is my test data:

    2.When I use a field as a filter, the result is normal:

    3.However, when I use the following measure as a filter, it only returns whether the row value is displayed:

    Measure = IF(MAX('Table'[Column2])=1,1,0)

    This does not guarantee that using a field as a filter will always produce the correct result. If you reference fields from other tables, the filtering relationship between the original table and the other tables may not meet the requirements, or issues may arise if the two tables have a many-to-many relationship.

     

    Please find the attached pbix relevant to the case.

     
    Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
     

    Best Regards,

    Leroy Lu

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

     

3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    1 year ago

    Fanis_Georg 

    Power BI does not add 1+1 as you see in Excel, each calculation is performed independently based on the filter context. Check your data for empy/blank values or any filters you have applied to the visuals. You may try

    Stock Count = SUMX( VALUES( 'tablename'[column name] ), 1 )


    or

    Stock Count = COUNTROWS ( VALUES( 'tablename'[column name] ) )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply from Fowmy , please allow me to provide another insight:

    Hi, Fanis_Georg 
    Thanks for reaching out to the Microsoft fabric community forum.

    The issue you're encountering is commonly due to using measures as visual-level filters. This causes the COUNT aggregation method's calculation process to not be directly affected by the visual-level filters. In other words, when you use an aggregation method on a field, using measures as visual-level filters will only determine whether the row values are displayed, but will not affect the calculation result itself.

     

    I recommend using the FILTER() function to include your filter conditions and then writing a measure to replace the implicit aggregation on the visual object.

     

    Here is a link to a similar issue:

    Solved: Re: Visualization data changes when using count fi... - Microsoft Fabric Community

     

    Additionally, you can use the ISINSCOPE() function to specifically modify the total section.

    For more details, please refer to the documentation:

    ISINSCOPE function (DAX) - DAX | Microsoft Learn
    HASONEVALUE function (DAX) - DAX | Microsoft Learn
    Hierarchies – DAX Patterns
     

    Here is my test process:

    1.First, this is my test data:

    2.When I use a field as a filter, the result is normal:

    3.However, when I use the following measure as a filter, it only returns whether the row value is displayed:

    Measure = IF(MAX('Table'[Column2])=1,1,0)

    This does not guarantee that using a field as a filter will always produce the correct result. If you reference fields from other tables, the filtering relationship between the original table and the other tables may not meet the requirements, or issues may arise if the two tables have a many-to-many relationship.

     

    Please find the attached pbix relevant to the case.

     
    Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
     

    Best Regards,

    Leroy Lu

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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, Fanis_Georg 
     It's been a while since we last interacted. Has your issue been resolved? If our answers did not solve your problem, please let us know the shortcomings of our solution.

    If our answers have helped you, please accept the answer you found effective as the solution. This will help many community members with similar issues.

     

    Of course, if you have your own solution, feel free to share it with us. Looking forward to your response, thank you in advance!

     

    Best Regards,

    Leroy Lu