Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |