Labelling data into category with Conditional Column

[saadghojaria]

Hi, 
I have the data which I want to tag with conditional column called Rev Type by categories called RevA or RevB. Here is my Column DAX code  which performs :
- to check if the user name starts with bp of any of the row of specific eq category then the Rev column will be RevA or else no username starts with bp will be RevB. 

- exclude all rows with other than current status as effective

Code:

Rev Type =

IF(

    CALCULATE(

        COUNTROWS(

            FILTER(

                ALL('Z'),

                LEFT('Z'[Username], 2) = "bp"

            )

        ),

        ALLEXCEPT('Z', 'Z'[Eq])

    ) > 0,

    "RevA",

    "RevB"

)

Current output : 

Rev Type (current)	Eq	Username	Current status
RevA	A	aval	Effective
RevA	A	bp02	Effective
RevA	A	aval	Effective
RevA	A	aval	Effective
RevA	C	mnil	Effective
RevA	C	rfer	Effective
RevA	C	vt	Effective
RevA	C	mnil	Effective
RevA	C	mnil	Effective
RevA	A		Draft
RevA	B		Draft
RevA	D		Draft
RevA	C		Draft
RevA	B	aval	Circulation
RevA	D	mnil	Circulation
RevA	C	vt	Circulation

Expected Output

Revision Type (expected)	Eq	Username	Current status
RevA	A	aval	Effective
RevA	A	bp02	Effective
RevA	A	aval	Effective
RevA	A	aval	Effective
RevB	C	mnil	Effective
RevB	C	rfer	Effective
RevB	C	vt	Effective
RevB	C	mnil	Effective
RevB	C	mnil	Effective
N/A	A		Draft
N/A	B		Draft
N/A	D		Draft
N/A	C		Draft
N/A	B	aval	Circulation
N/A	D	mnil	Circulation
N/A	C	vt	Circulation

[Anonymous · Accepted solution]

Thank you PhilipTreacy

Hi, @saadghojaria 

I used the following example data:

You can use the following DAX expression to correctly calculate your Rev Type

Column = IF (
    'Table'[Current status] = "Effective",
    VAR _table =
        ADDCOLUMNS (
            SUMMARIZE (
                FILTER ( 'Table', 'Table'[Current status] = "Effective" ),
                'Table'[Eq],
                'Table'[Username]
            ),
            "Isfit", SEARCH ( "bp", 'Table'[Username], 1, 0 )
        )
    VAR _eq =
        SELECTCOLUMNS ( FILTER ( _table, [Isfit] = 1 ), 'Table'[Eq] )
    VAR _table2 =
        ADDCOLUMNS (
            ALL ( 'Table' ),
            "Res", SWITCH ( TRUE (), 'Table'[Eq] IN _eq, "Rev_A", "RevB" )
        )
    VAR _current_eq = 'Table'[Eq]
    RETURN
        MAXX ( FILTER ( _table2, 'Table'[Eq] = _current_eq ), [Res] ),
    "N/A"
)

The results are as follows:

I've provided the PBIX file used this time below.

Best Regards

Jianpeng Li

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

[PhilipTreacy]

@saadghojaria 

It's not really clear what you want, can you please provide an example of the desired result you seek?

The code you have provided, what is that for?  The first section of code is seraching for the string "bp". the second piece of code is lookign for "bp" at the start of the string.

Please clarify.

Phil

[saadghojaria]

Hi Phil,

Thanks for looking into this, please check now if it makes more sense.