SWITCH TRUE same column contain multiple status

[Saxon10]

Hi,

I have 9 columns are UK, US, INDIA, SA, SRL, PAK,BAN ,AFG and ENG (from column B:J and column K is my final result in Excel), each columns contain multiple status.

UK	US	INDIA	SA	SRL	PAK	BAN	AFG	ENG
MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED
NOT REQUIRED	NOT MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	NOT REQUIRED
								NOT MATCHED

Matching Rules from column B to J:

Rule1

Column B = “Matched”

Column C: J = “Matched”

Column J = “Not Required”

Then return is “Completed”

Rule2

Column B = “Not Required”

Column C: J = “Matched”

Column J = “Not Required”

Then return is “Completed”

Rule3

Column B = “Not Required”

Column C: J = “Matched”

Column J = “Matched”

Then return is “Completed”

Rule5

Column B = “Not Required”

Column C: J = “Matched”

Column J = “Not Matched”

Then return is “Not Completed”

Rule6

Column C:I  any one of the columns are contain ”Not matched” then return Not completed when column B contain “Matched”, ”Not Required” and column J contain “Matched”, “Not Matched” and “Not Required”.

My Data desired result in column K and Data

UK	US	INDIA	SA	SRL	PAK	BAN	AFG	ENG	STATUS
MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT REQUIRED	COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT REQUIRED	COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	NOT COMPLETED
NOT REQUIRED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	NOT MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	NOT MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT MATCHED	NOT COMPLETED
NOT REQUIRED	NOT MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	NOT MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	MATCHED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	NOT MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	MATCHED	NOT MATCHED	NOT REQUIRED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	MATCHED	MATCHED	NOT COMPLETED
NOT REQUIRED	MATCHED	MATCHED	MATCHED	MATCHED	NOT MATCHED	NOT MATCHED	MATCHED	NOT MATCHED	NOT COMPLETED

[mahoneypat · Accepted solution]

The data should be unpivoted.  See the attached pbix in the Data2 query.  After that, a measure expression like this one can be used to get your result in a table visual with your Item column.

Status =
VAR vCtoI =
    CALCULATE (
        COUNTROWS ( DATA2 ),
        DATA2[Value] = "NOT MATCHED",
        FILTER (
            ALL ( DATA2[Attribute] ),
            NOT ( DATA2[Attribute]
                IN {
                "ENG",
                "UK"
            } )
        )
    ) > 0
VAR vEngUK =
    AND (
        CALCULATE (
            COUNTROWS ( DATA2 ),
            DATA2[Value] = "NOT REQUIRED",
            DATA2[Attribute] = "UK"
        ) > 0,
        CALCULATE (
            COUNTROWS ( DATA2 ),
            DATA2[Value] = "NOT MATCHED",
            DATA2[Attribute] = "ENG"
        ) > 0
    )
RETURN
    IF (
        OR (
            vCtoI,
            vEngUK
        ),
        "NOT COMPLETED",
        "COMPLETED"
    )

Regards,

Pat

[v-alq-msft · Accepted solution]

Hi, @Saxon10 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a calculated column or a measure as below.

Calculated column:

Status Column = 
var b=[UK]
var ci={[US],[INDIA],[SA],[SRL],[PAK],[BAN],[AFG]}
var j=[ENG]
return
SWITCH(
    TRUE(),
    b="Matched"&&COUNTROWS(ci)=COUNTROWS(FILTER(ci,[Value]="Matched"))&&j="Not Required",
    "Completed",
    b="Not Required"&&COUNTROWS(ci)=COUNTROWS(FILTER(ci,[Value]="Matched"))&&j="Not Required",
    "Completed",
    b="Not Required"&&COUNTROWS(ci)=COUNTROWS(FILTER(ci,[Value]="Matched"))&&j="Matched",
    "Completed",
    b="Not Required"&&COUNTROWS(ci)=COUNTROWS(FILTER(ci,[Value]="Matched"))&&j="Not Matched",
    "Not Completed",
    COUNTROWS(ci)<>COUNTROWS(FILTER(ci,[Value]="Matched")),
    "Not Completed"
)

Measure:

Status Measure = 
var b=MAX('Table'[UK])
var ci={MAX('Table'[US]),MAX('Table'[INDIA]),MAX('Table'[SA]),MAX('Table'[SRL]),MAX('Table'[PAK]),MAX('Table'[BAN]),MAX('Table'[AFG])}
var j=MAX('Table'[ENG])
return
SWITCH(
    TRUE(),
    b="Matched"&&COUNTROWS(ci)=COUNTROWS(FILTER(ci,[Value]="Matched"))&&j="Not Required",
    "Completed",
    b="Not Required"&&COUNTROWS(ci)=COUNTROWS(FILTER(ci,[Value]="Matched"))&&j="Not Required",
    "Completed",
    b="Not Required"&&COUNTROWS(ci)=COUNTROWS(FILTER(ci,[Value]="Matched"))&&j="Matched",
    "Completed",
    b="Not Required"&&COUNTROWS(ci)=COUNTROWS(FILTER(ci,[Value]="Matched"))&&j="Not Matched",
    "Not Completed",
    COUNTROWS(ci)<>COUNTROWS(FILTER(ci,[Value]="Matched")),
    "Not Completed"
)

Result:

Best Regards

Allan

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

[Saxon10]

Thank you so much for your time and help. This is exactly I am looking for.

[mahoneypat · Accepted solution]

The data should be unpivoted.  See the attached pbix in the Data2 query.  After that, a measure expression like this one can be used to get your result in a table visual with your Item column.

Status =
VAR vCtoI =
    CALCULATE (
        COUNTROWS ( DATA2 ),
        DATA2[Value] = "NOT MATCHED",
        FILTER (
            ALL ( DATA2[Attribute] ),
            NOT ( DATA2[Attribute]
                IN {
                "ENG",
                "UK"
            } )
        )
    ) > 0
VAR vEngUK =
    AND (
        CALCULATE (
            COUNTROWS ( DATA2 ),
            DATA2[Value] = "NOT REQUIRED",
            DATA2[Attribute] = "UK"
        ) > 0,
        CALCULATE (
            COUNTROWS ( DATA2 ),
            DATA2[Value] = "NOT MATCHED",
            DATA2[Attribute] = "ENG"
        ) > 0
    )
RETURN
    IF (
        OR (
            vCtoI,
            vEngUK
        ),
        "NOT COMPLETED",
        "COMPLETED"
    )

Regards,

Pat

[Saxon10]

Thank you. Your solution working fine.

[Saxon10]

thanks for your reply. I will check and update the feedback to you. 

[mahoneypat]

Here is one way to do it in the query editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZVNCoAgEIWvEq29RJhQUFliK+n+1+hnExXCqDOj7WaI+nzPeZNz9dhY2am2FkjVpG1l1LL25mqlHudB2aPehHs//CvxrlBg52u+zivzbJGllkL+dnF0nx44tyQPnh2LBfHyYV/luIi0k+DGIS8dYw0EDCHmCHDKp9wA8DGnDiHvSfAjELUNU8FZoJwTz+wx9Q835EZL2LCJhsMXN5Xs+HRTmQ7zhGDSyaGhykrxmodOFLG0eHnh2w4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UK = _t, US = _t, INDIA = _t, SA = _t, SRL = _t, PAK = _t, BAN = _t, AFG = _t, ENG = _t, STATUS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UK", type text}, {"US", type text}, {"INDIA", type text}, {"SA", type text}, {"SRL", type text}, {"PAK", type text}, {"BAN", type text}, {"AFG", type text}, {"ENG", type text}, {"STATUS", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CtoI", each List.RemoveLastN(List.RemoveFirstN(Record.ToList(_),1),1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "StatusNew", each if List.Count(List.Select([CtoI], each _ = "NOT MATCHED"))>0 or ([UK]="NOT REQUIRED" and [ENG] = "NOT MATCHED") then "NOT COMPLETED" else "COMPLETED")
in
    #"Added Custom1"

Regards,

Pat

[Saxon10]

thanks for your quick reply. The 9 column came from DAX measure. It's not part of the raw data. Can I able to apply the query editor? Still it will work? Can you please alternative solutions power bi visualisation if not achieved by DAX measure. 

[mahoneypat]

It can be done with DAX or with M (as shown).  Please share your actual raw data (or representative data in the same format), preferably as a link to a pbix file on Google Drive or OneDrive.

Regards,

Pat

[Saxon10]

thanks for your reply. Herewith attached the Excel and Power BI file for your refernece with same format.

Could you please advise both solution (DAX measure and MCode)

https://www.dropbox.com/scl/fi/3vc7cq6wjw69ij8jcwfws/SWITCH-MULTIPLE-COLUMN.xlsx?dl=0&rlkey=cysma0j3...

https://www.dropbox.com/s/10k7d15euk1z7ov/SWITCH%20MULTIPLE%20COLUMN.pbix?dl=0

Could you please let me know if you need more details.

[Saxon10]

can you please advise and help me anyone.