Forum Discussion

Tevon713's avatar
Tevon713
Helper V
7 months ago
Solved

Unique Count in Row convert

Hi all,   I have issue trying to convert simple excel formula in pbi.    Sorry I wasn't transparent in my initial post (ignore the table sample below and see sample data link). I have over millio...
Need Help
Tips and Tricks
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    6 months ago

    Hi Tevon713,
    Thank you for the follow-up and for testing the helper-table approach.

    The behaviour you are seeing occurs because Power BI measures are always evaluated based on the current filter context, so when a helper table is related back to the fact table, slicers such as Year, Region, or Product still propagate and change the result. This is why the unique count continues to vary (for example 7 instead of 14), even though the logic appears correct.

    If your requirement is to count accounts that appear exactly once in the entire dataset and keep that value constant regardless of filters, the uniqueness logic must explicitly ignore report filters. You can achieve this using the following measure, which evaluates uniqueness across the full dataset and removes all filters:

    Truly Unique Accounts :=
CALCULATE (
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                ALL ( 'Fact' ),
                'Fact'[Acct #],
                "RowCnt", COUNTROWS ( 'Fact' )
            ),
            [RowCnt] = 1
        )
    ),
    REMOVEFILTERS ( 'Fact' )
)

    This measure evaluates uniqueness at the Account # level and will consistently return 14, matching the intent of your original Excel logic, regardless of how the report is filtered.

    Thanks again for using the Microsoft Fabric Community Forum.

     

Tevon713's avatar
Tevon713
Helper V
6 months ago

Hi v-kpoloju-msft. I tried but can't seem to replicate the result I wanted. Filtering by 1 and creating helper table. The unique count should be 12, the helper table result yield 7.

 

Sample PBI 

v-kpoloju-msft's avatar
v-kpoloju-msft
Community Support
6 months ago

Hi Tevon713, 

Thanks for sharing the pbix file. The difference you are seeing (Test measure = 14 vs Sum of Uniq Ct = 12) comes down to granularity. Uniq Ct is a row-level flag, so when the same Account # appears in multiple rows (for example across different years, regions, or products), summing that column counts the same account more than once. The test measure, however, evaluates uniqueness at the Account # level, so it collapses those repeated rows and returns the distinct number of accounts. This is expected behaviour in Power BI and isn’t a calculation issue.

To resolve this, the calculation needs to align to a single grain. If your goal is to count truly unique accounts, the logic must be defined at the Account # level (for example via a distinct-count measure or an account-level table), rather than by summing a row-based helper column. Power BI behaves this way because measures are evaluated in filter context, while row flags are evaluated in row context. Once the grain is aligned, the result will be consistent, otherwise, a row-level total and an account-level distinct count should not be expected to match.

Refer these links:
1. https://learn.microsoft.com/en-gb/power-bi/transform-model/desktop-quickstart-learn-dax-basics#row-context-and-filter-context 
2. https://learn.microsoft.com/en-gb/power-bi/transform-model/desktop-calculated-columns 
3. https://learn.microsoft.com/en-gb/dax/distinctcount-function-dax 

Hope that clarifies. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Community Support
    6 months ago

    Hi Tevon713,
    Thank you for the follow-up and for testing the helper-table approach.

    The behaviour you are seeing occurs because Power BI measures are always evaluated based on the current filter context, so when a helper table is related back to the fact table, slicers such as Year, Region, or Product still propagate and change the result. This is why the unique count continues to vary (for example 7 instead of 14), even though the logic appears correct.

    If your requirement is to count accounts that appear exactly once in the entire dataset and keep that value constant regardless of filters, the uniqueness logic must explicitly ignore report filters. You can achieve this using the following measure, which evaluates uniqueness across the full dataset and removes all filters:

    Truly Unique Accounts :=
CALCULATE (
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                ALL ( 'Fact' ),
                'Fact'[Acct #],
                "RowCnt", COUNTROWS ( 'Fact' )
            ),
            [RowCnt] = 1
        )
    ),
    REMOVEFILTERS ( 'Fact' )
)

    This measure evaluates uniqueness at the Account # level and will consistently return 14, matching the intent of your original Excel logic, regardless of how the report is filtered.

    Thanks again for using the Microsoft Fabric Community Forum.

     

  • Tevon713's avatar
    Tevon713
    Helper V
    6 months ago

    Thanks v-kpoloju-msft for the context and understood. However, I attempted to create a helper table then link it back to fact table and count the row as suggested.

    It still not getting the correct unique count on granular level that doesn't affect when filtering.  This should be 14 unique count ie Test Measure. 

    Sample PBI