Forum Discussion
Calculated Column based on different values
I hope that you can help me. Let me try to explain you the data that I have and the result expected:
Having the following list
ID | Name | Status |
1 | Jane | Validated |
2 | Bill | Error |
3 | Bill | Affected |
4 | Bob | Warning |
5 | Chris | Affected |
6 | Jane | Error |
7 | Anne | Warning |
8 | Bill | Warning |
9 | Bob | Validated |
10 | Anne | Validated |
11 | Bob | Error |
12 | Anne | Error |
13 | Bill | Error |
14 | Anne | Validated |
I need to come up with the following table.
ID | Name | Status |
1 | Jane | Validated |
2 | Bill | Error |
9 | Bob | Validated |
5 | Chris | Affected |
10 | Anne | Validated |
The idea is that it will look for the status per person and using the table below will provide the correct ID and status by name providing always the first value based on the priority
| Priority |
|
Validated | 1 |
|
Error | 2 | Smaller ID |
Affected | 2 | |
Warning | 3 |
|
For example.
- For Jane it returns validated and the ID 1 as “Validated” is the first priority so as soon as we find the first status validated it will put Jane as Validated with the ID 1
- For Bill it returns Error and with ID 2 because there is no record in status Validated (which is priority 1), and as Error and Affected both of them have priority 2, then it returns the first one which is Error and with ID 2
- For Bob, the first result is Warning (priority 3), but we see a “Validated” with ID 9, so we get this one
My idea was to create a calculated column to add an additional column to include the Final status
ID | Name | Status | Final Status |
1 | Jane | Validated | Validated |
2 | Bill | Error | Error |
3 | Bill | Affected | Error |
4 | Bob | Warning | Validated |
5 | Chris | Affected | Affected |
6 | Jane | Error | Validated |
7 | Anne | Warning | Validated |
8 | Bill | Warning | Error |
9 | Bob | Validated | Validated |
10 | Anne | Validated | Validated |
11 | Bob | Error | Validated |
12 | Anne | Error | Validated |
13 | Bill | Error | Error |
14 | Anne | Validated | Validated |
I have tried many different ways but I am not able to really get it right ☹ Can you help me?
Hi vzbkb1 ,
I understand, you can create another column:
Column 2 = IF ( 'Table'[ID] = MINX ( FILTER ( 'Table', 'Table'[Name] = EARLIER ( 'Table'[Name] ) && 'Table'[Status] = 'Table'[Column] ), 'Table'[ID] ), 1, 0 )Result:
Now it works:
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Great!!! Yes, that it was I was looking for. Thanks a lot for your help and your quick responses
Hi vzbkb1 ,
I understand😊.
Modify the formula:
Column = VAR _T = FILTER ( 'Table', 'Table'[Name] = EARLIER ( 'Table'[Name] ) ) RETURN IF ( COUNTROWS ( FILTER ( _T, [Status] = "Validated" ) ) > 0, "Validated", IF ( COUNTROWS ( FILTER ( _T, [Status] IN { "Error", "Affected" } ) ) > 0, MAXX ( FILTER ( _T, [ID] = MINX ( FILTER ( _T, [Status] IN { "Error", "Affected" } ), [ID] ) ), 'Table'[Status] ), IF ( COUNTROWS ( FILTER ( _T, [Status] = "Warming" ) ) > 0, "Warming" ) ) )I modify the sample and get correct result.
Best Regards,
Community Support Team _ kalyj
11 Replies
Hi vzbkb1 ,
According to your description, here's my solution.
Create a calculated column.
Column = VAR _T = FILTER ( 'Table', 'Table'[Name] = EARLIER ( 'Table'[Name] ) ) RETURN IF ( COUNTROWS ( FILTER ( _T, [Status] = "Validated" ) ) > 0, "Validated", IF ( COUNTROWS ( FILTER ( _T, [Status] = "Error" ) ) > 0, "Error", IF ( COUNTROWS ( FILTER ( _T, [Status] = "Affected" ) ) > 0, "Affected", IF ( COUNTROWS ( FILTER ( _T, [Status] = "Warming" ) ) > 0, "Warming" ) ) ) )Get the result:
If you want to get the first ID, create a measure:
Measure = IF ( MAX ( 'Table'[ID] ) = MINX ( FILTER ( ALL ( 'Table' ), 'Table'[Name] = MAX ( 'Table'[Name] ) && 'Table'[Status] = 'Table'[Column] ), 'Table'[ID] ), 1, 0 )Put the measure in the visual filter and set its value to 1.
After apply filter, get the correct result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It works perfectly thank you 🙂 I just had an small issue now. I will need to use the filster not only to this table but also to the page where I have some counters, and the issue is that I cannot use the measure created to put it as a filter page. ¿Is possible to have a new column with the same values as those in the measure created? I have tried, but I always get 0 😞
Hi vzbkb1 ,
Sorry maybe I'm not very clear about your desired result. Isn't this:
If not, could you please show me the result.
Best Regards,
Community Support Team _ kalyjThe result is perfect. My question came mainly because I want to use the mease created to filter the page but that it is not possible. For example, if I try to include a card with the total of names, I get 14 but really I only want to get 5. That is the reason that I was thinking that if instead the measure I could have another column to contain de values 0 and 1 (same as the mesure that you proposed)
Many thanks. It works great now in both casuisticas.