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