Create a Column Based on Conditions

[Tee]

Hi everyone,

I am new to power bi and need help with regard to the following requirements. 

Reference No	MPL	NR	CRTRF	Code
200	Agriculture	New	New	0
200	Agriculture	New	New	0
200	Agriculture	New	New	1
200	Agriculture	New	New	0
200	Agriculture	New	New	0
200	Agriculture	New	New	0
228	Agriculture	New	New	0
228	Agriculture	New	New	0
228	Agriculture	New	New	0
268	Agriculture	New	New	0
26	Agriculture	Returning	RWC	0
26	Agriculture	Returning	RWC	0
26	Agriculture	Returning	RWC	0
353	Agriculture	Returning	ROC	0
353	Agriculture	Returning	ROC	0
41	Biology	New	New	0
41	Biology	New	New	0
41	Biology	New	New	0
41	Biology	New	New	0

I want to create a new column BCLL.

- If MPL = Agriculture, NR = New, and Code = 1 in any of the rows with the same Reference No, return New Student.

From the above table, the first 6 rows have the same reference no and only 1 has a corresponding code of 1.

Because one of the codes is 1, MPL = Agriculture, and NR = New, the output for all the 6 rows should be New Student.

- If MPL = Agriculture, NR = New and Code = 0 for all the row with the same Reference No, return Addional.

From the above table, rows 7 to 10 have the same reference number, and code = 0 for all of them.

Because all the code is 0, MPL = Agriculture, and NR = New, the output for all the 4 rows should be Additional.

- If MPL is not equal to Agriculture and NR is not equal to New, return column CRTRF.

From the above table, NR is not equal to New in rows 11 to 15, they will return column CRTRF.

- If MPL is not equal to Agriculture and NR is not equal to New, return column CRTRF.

From the above table, MPL is not equal to Agriculture in rows 16 to 19, they will return column CRTRF.

The final output will look like the following table.

Please note that the actual table consists of more than 50,000 rows, this is just a sample.

Reference No	MPL	NR	CRTRF	Code	BCLL
200	Agriculture	New	New	0	New Student
200	Agriculture	New	New	0	New Student
200	Agriculture	New	New	1	New Student
200	Agriculture	New	New	0	New Student
200	Agriculture	New	New	0	New Student
200	Agriculture	New	New	0	New Student
228	Agriculture	New	New	0	Additional
228	Agriculture	New	New	0	Additional
228	Agriculture	New	New	0	Additional
268	Agriculture	New	New	0	Additional
26	Agriculture	Returning	RWC	0	RWC
26	Agriculture	Returning	RWC	0	RWC
26	Agriculture	Returning	RWC	0	RWC
353	Agriculture	Returning	ROC	0	ROC
353	Agriculture	Returning	ROC	0	ROC
41	Biology	New	New	0	New
41	Biology	New	New	0	New
41	Biology	New	New	0	New
41	Biology	New	New	0	New

Please assist.

Thank you.

[Samarth_18 · Accepted solution]

Hi @Tee ,

Please try this:-

BCLL =
SWITCH (
    TRUE (),
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[MPL] = "Agriculture"
                && 'Table'[NR] = "New"
                && [Code] = 1
                && 'Table'[Reference No] = EARLIER ( 'Table'[Reference No] )
        )
    ) > 0, "New Student",
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[MPL] = "Agriculture"
                && 'Table'[NR] = "New"
                && [Code] = 0
                && 'Table'[Reference No] = EARLIER ( 'Table'[Reference No] )
        )
    ) > 0, "Addional",
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[MPL] <> "Agriculture"
                && 'Table'[NR] <> "New"
                && [Code] = 0
                && 'Table'[Reference No] = EARLIER ( 'Table'[Reference No] )
        )
    ) >= 0, 'Table'[CRTRF]
)

Output:-

Thanks,

Samarth

[Tee]

Thank you for your response.

I encountered the following error:

DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

I also want to note that I don't have [Reference] table as in the sample of the code below;

No]) ), [Reference]) , "New Student" ,

Do you mean Reference No column?

Thank you.

[amitchandak]

@Tee , Try a new column

New Column =
Switch( True() .
[Code] = Maxx(filter(Table, Table[MPL] = "Agriculture" && Table[NR] = "New" && [Code]= 1 && [Reference No] =earlier([Reference No]) ), [Reference]) , "New Student" ,
[Code] = Maxx(filter(Table, Table[MPL] = "Agriculture" && Table[NR] = "New" && [Code]= 0 && [Reference No] =earlier([Reference No]) ), [Reference]) ,"Addional",
[Code] = Maxx(filter(Table, Table[MPL] <> "Agriculture" && Table[NR] <> "New" && [Code]= 0 && [Reference No] =earlier([Reference No])), [Reference]) ,"CRTRF")