Forum Discussion

thiendt5's avatar
thiendt5
Frequent Visitor
6 years ago
Solved

Separate duplicate values with order number by columns

Hello all!
I'm new to Power BI and I'm creating Error code tracking in Power BI.
Please help to see in below example:

I have the table below. Each IMEI is tested many times and generate many diffirent error codes:


Is there any DAX function formula could create new columns like this? I just want to show all error code of a IMEI by rows

 

I tried IF function but it didn't work
ERROR_1st = IF([Test_time]=1,[Error_code])
ERROR_2nd = IF([Test_time]=2,[Error_code])

The result i've got as below and I can't see all error code at a time when filtering:

 

Please suggest for this case. Thanks!

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, thiendt5 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a calculated column as below.

    ERROR_Num = 
SWITCH(
    'Table'[Test_time],
    1,"ERROR_1st",
    2,"ERROR_2nd",
    3,"ERROR_3nd", 
    4,"ERROR_4th",
    5,"ERROR_5th"
)

     

    Then you may use 'Matrix' visual to display the result.

     

    Or you may create a calculated table with following dax.

    Result Table = 
ADDCOLUMNS(
    DISTINCT('Table'[IMEI]),
    "ERROR_1st",
    CONCATENATEX(
        FILTER(
            'Table',
            [IMEI]=EARLIER('Table'[IMEI])&&
            [Test_time]=1
        ),
        [Error_code],
        " "
    ),
    "ERROR_2nd",
    CONCATENATEX(
        FILTER(
            'Table',
            [IMEI]=EARLIER('Table'[IMEI])&&
            [Test_time]=2
        ),
        [Error_code],
        " "
    ),
    "ERROR_3rd",
    CONCATENATEX(
        FILTER(
            'Table',
            [IMEI]=EARLIER('Table'[IMEI])&&
            [Test_time]=3
        ),
        [Error_code],
        " "
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

    • thiendt5's avatar
      thiendt5
      Frequent Visitor
      6 years ago

      amitchandak I'm sorry but pivot do not work. I created [Test_time] column by DAX so it can not be shown in Query Editor.

      Could you suggest filter and grouping DAX function for this case?

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        thiendt5 , you can create a new table using summarize 

        like

        summarize(Table, Table[itei] , ",ERROR_1st" ,maxX(Table, IF([Test_time]=1,[Error_code])) , "ERROR_2nd",maxX(Table, IF([Test_time]=2,[Error_code])))

         

        Add other columns 

         

        Or this how you can have new column in M/edit query

        ERROR_1st = if [Test_time]=1 then [Error_code]


        Then you can use aggregate column 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    thiendt5 So maybe like the following:

    Column = "ERROR_" & [Test_time]

    You could then use that in a matrix visualization as the Column field. 

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, thiendt5 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a calculated column as below.

    ERROR_Num = 
SWITCH(
    'Table'[Test_time],
    1,"ERROR_1st",
    2,"ERROR_2nd",
    3,"ERROR_3nd", 
    4,"ERROR_4th",
    5,"ERROR_5th"
)

     

    Then you may use 'Matrix' visual to display the result.

     

    Or you may create a calculated table with following dax.

    Result Table = 
ADDCOLUMNS(
    DISTINCT('Table'[IMEI]),
    "ERROR_1st",
    CONCATENATEX(
        FILTER(
            'Table',
            [IMEI]=EARLIER('Table'[IMEI])&&
            [Test_time]=1
        ),
        [Error_code],
        " "
    ),
    "ERROR_2nd",
    CONCATENATEX(
        FILTER(
            'Table',
            [IMEI]=EARLIER('Table'[IMEI])&&
            [Test_time]=2
        ),
        [Error_code],
        " "
    ),
    "ERROR_3rd",
    CONCATENATEX(
        FILTER(
            'Table',
            [IMEI]=EARLIER('Table'[IMEI])&&
            [Test_time]=3
        ),
        [Error_code],
        " "
    )
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.