create a new column with distinct value from two column combination

[cnwonge]

Dear brother,

I am newbie in DAX, and would like to have Dax code to achieve,

Data table is simplifed, it consists of hundred thousand record,

Would like to define them as the catagory logic into five options depends the "WBS" (A or B) and "Value"(1 or 0) combinaton.

Many thanks 

[v-shex-msft · Accepted solution]

Hi @cnwonge,

I think you need to add a group column for each A,B group.

Sample steps:

Then you can reference above table to transpose table and group records.

Full query:

let
    Source  = Records,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"WBS", type text}, {"value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Item", "Group ID"}, {{"Records", each Table.PromoteHeaders(Table.Transpose(Table.SelectColumns(_,{"WBS","value"})), [PromoteAllScalars=true]), type table}}),
    #"Expanded Records" = Table.ExpandTableColumn(#"Grouped Rows", "Records", {"A", "B"}, {"A", "B"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Records",{{"Item", type text}, {"Group ID", Int64.Type}, {"A", Int64.Type}, {"B", Int64.Type}})
in
    #"Changed Type1"

Salve changes and exit query editor.

Write a measure to check records between transpose table and category table.

Category tag = 
VAR _currentitem =
    SELECTEDVALUE ( 'Records Transpose'[Item] )
VAR seleceted =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'Records Transpose' ), [Item] = _currentitem ),
        "A", [A],
        "B", [B]
    )
RETURN
    CONCATENATEX (
        FILTER (
            ALL ( Category ),
            CONTAINS ( seleceted, [A], Category[A], [B], Category[B] )
        ),
        [Category],
        ","
    )

Notice: I attach sample file below.

Regards,

Xiaoxin Sheng