Calculated Column based on different values

[vzbkb1]

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?

[v-yanjiang-msft · Accepted solution]

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 _ kalyj

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

[vzbkb1 · Accepted solution]

Great!!!  Yes, that it was I was looking for.  Thanks a lot for your help and your quick responses

[vzbkb1]

Many thanks.  It works great now in both casuisticas.

[vzbkb1]

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 😞

[v-yanjiang-msft]

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 _ kalyj

[vzbkb1]

The 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)

[v-yanjiang-msft · Accepted solution]

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 _ kalyj

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

[vzbkb1]

Sorry to come back again, but I have found a situation where it is not given me the correct data.

It works OK if the status is “validated” and “Warning”, but in the event that we have the status Affected and Error, it always returns as good the record with the status Error and not the first of those two.

For example, with this table

Is providing the result

For Bill is correct that the result is “error” with ID 2 as it is the first one, but in the case of Bob, it should give us the result of “Affected” with ID 9 as it has the smallest ID combining Affected and Error. 

I was thinking about adding a SORT BY in the FILTER  in the creation of the “Column” but I do not know how to put it. 

Can you help me?  

[v-yanjiang-msft]

Hi @vzbkb1 ,

Sorry I'm not very clear. In my understanding, Error takes precedence over Affected.

So for Bob, the first choose should be 15 which is Error. Note the forth column is just to define the first priority per person.

Best Regards,
Community Support Team _ kalyj

[vzbkb1]

Sorry, I think that I did not express myself correctly ☹ 

Affected and Error has the same priority, and the idea is to get the smallest ID of the combination of the records with status affected and Error.  

In the case of Bob, we have three records that will have the same priority (Affected with ID 9, Affected with ID 11 and Error with ID 15), and the result in this case will be Affected as it was the lower ID (9)

Hope that it clarifies better

[v-yanjiang-msft · Accepted solution]

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

[vzbkb1 · Accepted solution]

Great!!!  Yes, that it was I was looking for.  Thanks a lot for your help and your quick responses

[v-yanjiang-msft]

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 _ kalyj

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