Distinct Identifier

[awitt]

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. 

[Zubair_Muhammad]

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

[awitt]

@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? 

[awitt]

@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! 

[Zubair_Muhammad]

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

[Zubair_Muhammad · 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 )