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.
I have a large dataset where reach ID/Account number column has multiple rows. Each row transaction has 1 of 4 statuses and I would like to get to an ID/Account level. The 4 possible statuses in the column are: Ready, Building, Building Materials, and Verification.
The logic should go like this:
If any row for the ID/Account has a status of Verification, the ID/Account status should be Verification regardless of any of the other statuses being present.
If any row for the ID/Account doesn't have Verification but has Building Materials, then the ID/Account status should be Building Materials.
If any row for the ID/Account doesn't have Verificayion or Building Materials, but has Building, then the ID/Account status should be Building.
If any row for the ID/Account doesn't have Verification, Building Materials, or Building but has Ready, then the ID/Account status should be Ready.
I'm currently using SWITCH to create a new column:
Solved! Go to Solution.
You might need to create an intermediary column that assigns numeric values to each status if you are nit handling this case :
Numeric Status =
SWITCH(
'Table'[Row_Level_Status],
"Verification", 4,
"Building Materials", 3,
"Building", 2,
"Ready", 1,
0
)
Then :
Acct Lvl Numeric Status =
CALCULATE(
MAX('Table'[Numeric Status]),
ALLEXCEPT('Table', 'Table'[ID])
)
and finally :
Final Status =
SWITCH(
[Acct Lvl Numeric Status],
4, "Verification",
3, "Building Materials",
2, "Building",
1, "Ready",
"Needs Review"
)
Thank you for the quick response. I took the response to mean that I'll need to create an additional column for 'Final Status'. When I do just the first portion above, I'm receiving "Cannot convert value 'TX123456' of type Text to type True/False". Is this due to the ID having both alpha and numeric characters?
You might need to create an intermediary column that assigns numeric values to each status if you are nit handling this case :
Numeric Status =
SWITCH(
'Table'[Row_Level_Status],
"Verification", 4,
"Building Materials", 3,
"Building", 2,
"Ready", 1,
0
)
Then :
Acct Lvl Numeric Status =
CALCULATE(
MAX('Table'[Numeric Status]),
ALLEXCEPT('Table', 'Table'[ID])
)
and finally :
Final Status =
SWITCH(
[Acct Lvl Numeric Status],
4, "Verification",
3, "Building Materials",
2, "Building",
1, "Ready",
"Needs Review"
)
Use CALCULATE combined with MAXX to find the highest priority status for each ID/Account :
Acct Lvl Status =
CALCULATE(
MAXX(
VALUES('Table'[ID]),
SWITCH(
TRUE(),
COUNTROWS(FILTER('Table', 'Table'[Row_Level_Status] = "Verification")) > 0, 4,
COUNTROWS(FILTER('Table', 'Table'[Row_Level_Status] = "Building Materials")) > 0, 3,
COUNTROWS(FILTER('Table', 'Table'[Row_Level_Status] = "Building")) > 0, 2,
COUNTROWS(FILTER('Table', 'Table'[Row_Level_Status] = "Ready")) > 0, 1,
0
)
),
'Table'[ID]
)
Final Status =
SWITCH(
[Acct Lvl Status],
4, "Verification",
3, "Building Materials",
2, "Building",
1, "Ready",
"Needs Review"
)
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 |
---|---|
98 | |
96 | |
75 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |