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 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 AColumn BColumn C
Acct #Uniq CtPrior Yr Uniq Ct
94231 

9331

1 
8755 1
5694 1
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.

     

22 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    7 months ago

    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?

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        7 months ago

        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?

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    7 months ago

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

     

  • danextian's avatar
    danextian
    Super User
    7 months ago

    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's avatar
      Tevon713
      Helper V
      7 months ago

      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's avatar
        danextian
        Super User
        7 months ago

        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?

  • krishnakanth240's avatar
    krishnakanth240
    Super User
    7 months ago

    Hi Tevon713 

     

    Can you please provide some more sample data of records to work. Also just to clarify requirement is there a date column, if it is there please add it as a column in sample data of 50 records and share. Thank you!

  • danextian's avatar
    danextian
    Super User
    7 months ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 months ago

    Hi,

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

    • Tevon713's avatar
      Tevon713
      Helper V
      7 months ago

      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's avatar
        Ashish_Mathur
        Super User
        7 months ago

        Still very confusing.  Try this measure

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