Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
awitt
Helper III
Helper III

Distinct Identifier

I have a dataset which i need to add a column that labels whether or not a store location is unique. When a store has multiple products my data is providing the total units for all products in the column next for both products, effectively doubling the number. So for WalmartDallas, only 100 total products were sold - it may have been 70 apples and 30 bananas, we dont know. But if you were to sum the total units column, it would appear this store sold 200 total units which is incorrect. 

 

What i wan to create is the highlighted column. I know for specific calculations I could just do an average total units per distinct store-city, but for other applications it would be eaisier to have the identifying column. 

 

Capture.PNG

1 ACCEPTED SOLUTION

@awitt 

 

Infact the formula can be reduced to following. If you need result as Binary, you can chantge the data type to Binary from the Modelling tab>>formatting section. 1 is equivalent to TRUE, 0 is equivalent to FALSE

 

Column Formula =
VAR myrank =
    RANKX (
        CALCULATETABLE (
            'TableName',
            ALLEXCEPT ( 'TableName', 'TableName'[Store], 'TableName'[City] )
        ),
        [Product],
        ,
        ASC,
        DENSE
    )
RETURN
    IF ( myrank = 1, 1, 0 )

View solution in original post

5 REPLIES 5
Zubair_Muhammad
Community Champion
Community Champion

@awitt 

 

Try this as a Calculated Column

 

Column =
VAR IsUnique =
    CALCULATE (
        COUNTROWS ( 'TableName' ),
        ALLEXCEPT ( 'TableName', 'TableName'[Store], 'TableName'[City] )
    ) = 1
RETURN
    IF (
        IsUnique,
        1,
        RANKX (
            CALCULATETABLE (
                'TableName',
                ALLEXCEPT ( 'TableName', 'TableName'[Store], 'TableName'[City] )
            ),
            [Product],
            ,
            DESC,
            DENSE
        ) - 1
    )

@Zubair_Muhammad This is almost accurate. I have a lot values that end up as 2 or more. One goes up to 23. Is it possible to make it a bianary result like a Yes or No? 

@Zubair_Muhammad So this is the result i'm getting. When really i need the first instance of a store location to read as 1 and all the other instances to read as 0. Sorry for not explaining that at the top, my bad! 

 

Capture.PNG

@awitt 

 

Sorry for late reply. Here is the revised formula

 

Column =
VAR IsUnique =
    CALCULATE (
        COUNTROWS ( 'TableName' ),
        ALLEXCEPT ( 'TableName', 'TableName'[Store], 'TableName'[City] )
    ) = 1
RETURN
    IF (
        IsUnique,
        1,
        VAR myrank =
            RANKX (
                CALCULATETABLE (
                    'TableName',
                    ALLEXCEPT ( 'TableName', 'TableName'[Store], 'TableName'[City] )
                ),
                [Product],
                ,
                ASC,
                DENSE
            )
        RETURN
            IF ( myrank = 1, 1, 0 )
    )

@awitt 

 

Infact the formula can be reduced to following. If you need result as Binary, you can chantge the data type to Binary from the Modelling tab>>formatting section. 1 is equivalent to TRUE, 0 is equivalent to FALSE

 

Column Formula =
VAR myrank =
    RANKX (
        CALCULATETABLE (
            'TableName',
            ALLEXCEPT ( 'TableName', 'TableName'[Store], 'TableName'[City] )
        ),
        [Product],
        ,
        ASC,
        DENSE
    )
RETURN
    IF ( myrank = 1, 1, 0 )

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.