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 co...
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 )
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! 

     

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      6 years ago

      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's avatar
        Zubair_Muhammad
        Community Champion
        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 )