Forum Discussion

awitt's avatar
awitt
Helper III
6 years ago
Solved

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. 

 

Need Help
Show and Tell
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    6 years ago

    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 )

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    6 years ago

    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's avatar
      awitt
      Helper III
      6 years ago

      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's avatar
        awitt
        Helper III
        6 years ago

        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!