Unique Count in Row convert

[Tevon713]

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 million row in excel model. I want to create table output that works across all various dimension based on filter.

Is it just simple dynamic measure distinct count across the row then union of selectcolumn?

 Excel Formula:
Uniq Ct = IF (C2=1, " ", IF (COUNTIF($A$2:A5,A5)=1, 1, " "))

Column A	Column B	Column C
Acct #	Uniq Ct	Prior Yr Uniq Ct
9423	1
9331	1
8755		1
5694		1

[danextian]

Still unclear what you want to achieve. But try this:

Test measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Acct #] ),
    KEEPFILTERS ( 'Table'[Prior Yr Uniq Ct] <> 1 )
)
-- or ISBLANK('Table'[Prior Yr Uniq Ct])

[Tevon713]

Thanks @danextian.

Just a test, if I ignore the prior yr uniq ct column and imported in another month. Using the same logic for excel uniq ct formula = 12 and test measure = 14, difference of 2. How to keep the distinct count measure truly unique regardless of the facts filter?

Sample PBI Link 

[danextian]

This is unclear. What is your definition of truly unique? Using your sample pbix and with filters applied, what results do you expect adn why?

[Tevon713]

Thanks @danextian. Count if the acct # is truly unique (appear only once throughout the dataset) and distinct regardless of the date (and other facts). The test measure should match the sum of uniq ct ( ie the orginal excel uniq formula to begin with) -import it in as a test case.

[v-kpoloju-msft]

Hi @Tevon713,

Thank you for reaching out to the Microsoft Fabric Community Forum and thanks for the clarification.

The behaviour you are seeing is expected and comes from a core difference between Excel and Power BI. Your Excel helper column fixes “uniqueness” row-by-row and does not re-evaluate when filters are applied, whereas Power BI measures are always recalculated based on the current filter context (date, region, product, etc.). Because of this, DISTINCTCOUNT will change as you slice the data, which is why your Excel result and the test measure don’t always match.

If your requirement is to count Account #s that appear exactly once in the entire dataset and keep that result stable regardless of filters, the clean approach is to define global uniqueness first. You can do this by creating a small helper table that ignores filters and keeps only accounts with a total row count of 1 (for example, using SUMMARIZE(ALL(Table), Table[Acct #], "Cnt", COUNTROWS(Table)) and filtering where Cnt = 1). Relate this table back to your fact table on Account #, and then use a simple COUNTROWS measure on the helper table. This mirrors the intent of your Excel logic while remaining reliable and scalable in Power BI.

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.

[Tevon713]

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]

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-c... 
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.

[Tevon713]

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 

[v-kpoloju-msft · Accepted solution]

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.

[Ashish_Mathur]

Hi,

Cannot understand the formula logic in your Excel file.  Please explain the logic in simple language.

[Tevon713]

Sorry typo it suppose to lock the first cell then count and seek entire column F if there doesn't exist a count in year prior flag column H. 

Ideally if I add monthly data then column H doesn't really matter if it just "distinctcounts" column F, how to keep it dynamic across different dimensions based on filter?

=IF(H2=1,"",IF(COUNTIF($F$2:F2,F2)=1,1,""))

[Ashish_Mathur]

Still very confusing.  Try this measure

Measure = calculate(distinctcount(Data[Acct #]),Data[Prior Yr Uniq Ct]<>1)

[danextian]

Hi @Tevon713 

I am unclear what you're trying to achieve. If those two columns exist in your data source, you can simply SUM ('table'[column]) to get their individual sums. Otherwise, please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

[Tevon713]

Sorry I repost with sample data

[cengizhanarslan]

In Power BI you normally don’t create a “flag” column like Excel to then “count” later. You can get the same result more cleanly with a measure (recommended). If you truly need a calculated column that mimics your Excel helper, you can do that too.

Measure:

Unique Accts (not prior yr) =
CALCULATE (
    DISTINCTCOUNT ( T[Acct #] ),
    T[Prior Yr Uniq Ct] <> 1
)

Calculated Column (if needed):

Uniq Ct (column) =
VAR IsPrior = 'T'[Prior Yr Uniq Ct] = 1
VAR Acct    = 'T'[Acct #]
VAR FirstRowForAcct =
    CALCULATE (
        MIN ( 'T'[RowId] ),
        FILTER ( 'T', 'T'[Acct #] = Acct )
    )
RETURN
IF ( IsPrior, BLANK(), IF ( 'T'[RowId] = FirstRowForAcct, 1, BLANK() ) )

[Tevon713]

Thanks @cengizhanarslan now I thinking more about it if filtering across different dimension this not distinct

[ryan_mayu]

@Tevon713 

not clear about the formula. why it's 1 for 9331. When you drag down the excel formula, A5 will change to A6. However, A6 is blank in your sample data. Could you pls clarify this?

[Tevon713]

I have repost with sample excel model.

[ryan_mayu]

@Tevon713 

is your excel formula correct?

=IF(H2=1,"",IF(COUNTIF(F2:F2,F2)=1,1,""))

the expected output is the same as  =IF(H2=1,"",1)

could you explain the logic?

[Tevon713]

Sorry typo it suppose to lock the first cell then count and seek in column F.

Ideally if I add monthly data then column H doesn't really matter if it just "distinctcounts" column F, how to keep it dynamic across different dimensions based on filter?

=IF(H2=1,"",IF(COUNTIF($F$2:F2,F2)=1,1,""))