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 diffi...
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.

  • 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