Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
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 So maybe like the following:
Column = "ERROR_" & [Test_time]
You could then use that in a matrix visualization as the Column field.
@thiendt5 , You have pivot in power query. Otherwise, you have to create new column.
https://radacad.com/pivot-and-unpivot-with-power-bi
The option you want to try need filter and grouping. If pivot do not work
@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?
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |