Calculated Column Identifier based on if a particular condition is satisfied in Data Table

[JKPBI]

Hi all, it's hard to put into words for the title what I'm looking for, but (hopefully) simpler in the DAX

Really, what I'm looking to do is create a calculated column in my lookup table that will show TRUE for any user that has any actions which had multiple 'parts', and label them as a 'Multi-Part Person' forever if they had even one action which needed multiple parts.

See the below example, looking to populate with the highlighted.

The two tables have an INACTIVE relationship between them, which I'm having issues trying to overcome. I was trying to do this using CONTAINS() but I think it's too much of a zero-sum function that was returning if any of them fulfilled the criteria.

Table 1 (Data)

User Record	Name	Date	Action Number	Action Parts
11	Jon	1/2/2022	445	0
22	Bob	1/3/2022	441	0
11	Jon	1/4/2022	667	0
22	Bob	1/5/2022	778	5
33	Sue	1/6/2022	440	10
44	Jill	1/7/2022	999	0

Table 2 (Lookup)

User Record	Name	Address	Other Demographic	Multi-Part Person
11	Jon	Jon Address	Jon Demographic	FALSE
22	Bob	Bob Address	Bob Demographic	TRUE
33	Sue	Sue Address	Sue Demographic	TRUE
44	Jill	Jill Address	Jill Demographic	FALSE

Thank you for any help!

[JKPBI]

Hi Tom:

Yes, actually that does work, but I think I've failed to illustrate the whole problem.

There will be many records that have [Action Parts] which are 1 .. so I would not be looking for them to be TRUE but will be in your example because it's SUMing them. 

Having just run it, I have Users who have many records with only 1 Action Part that are coming up TRUE just because the SUM of all the 1 parts.

Apologies for the oversight, and thank you for the help, especially if there's a way around this new part.

[JKPBI]

@tackytechtom I wonder if a MAX function can plug in there and if it's greater than 1? I can't seem to get it to work

[v-yanjiang-msft · Accepted solution]

Hi @JKPBI ,

According to your description, I modify your sample data.

Table1:

Here's my solution. Create a calculated column.

Multi-Part Person =
IF (
    SUMX (
        FILTER (
            'Table1',
            'Table1'[User Record] = EARLIER ( Table2[User Record] )
                && 'Table1'[Action Parts] <> 1
        ),
        'Table1'[Action Parts]
    ) > 0,
    TRUE,
    FALSE
)

Get the correct result.

I attach my sample below for 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.

[tackytechtom]

Hi @JKPBI ,

How about this:

Here the DAX code for the calculated column:

Multi-Part Person = 
IF ( 
    Table34b[User Record] in ( FILTER ( VALUES ( Table34a[User Record] ), CALCULATE ( SUM ( 'Table34a'[Action Parts] ) > 0 ) ) ), 
    TRUE, 
    FALSE 
)

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/