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.
I have a table currently in this format:
Case # | Activity # | Action | Activity Order | Activity Due Date |
246810 | x1234 | Investigate | 1 | 1/1/2020 |
246810 | x1236 | Contain | 2 | 2/5/2020 |
246810 | x1237 | Resolve | 3 | 3/5/2020 |
998895 | x1560 | Investigate | 1 | 1/5/2020 |
998895 | x1582 | Implement | 2 | 3/2/2020 |
998895 | x1601 | Communicate | 3 | 4/6/2020 |
998895 | x1605 | Resolve | 4 | 7/2/2020 |
Each case can have several activities and each activity has a specific action. For each grouping by the case #, I want to be able to have a table or result like this:
Case # | Has Contain Action? |
246810 | Yes |
998895 | No |
Case #246810 has a contain action, but Case #998895 does not have a contain action. How would I be able to find a string for each case #?
Solved! Go to Solution.
= Table.Group(#"Changed Type2", {"Case #"}, {{"Check", each if Text.Contains(Text.Combine([Action], " "), "Contain", Comparer.OrdinalIgnoreCase) then "Yes" else "No"}})
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZA9C8MgFEX/i3Pg6fMjOnfK2jVkCEWKEE0hNuTn15eWkJY6XK7C4Xqw7xkqYwVnDdsESlW6S6tfcriP2ZeboIAA5MjZ0PzwpvRlTnkMqZyQArrCtqWvfpmnlXYl5cw6Z63TO6sNr3hUeEsvd/Ex+ehT/phIwL+04WK3jvGZwu29TjYKTIXXX+b0R+2xPbwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Case #" = _t, #"Activity #" = _t, Action = _t, #"Activity Order" = _t, #"Activity Due Date" = _t]),
#"Grouped Rows" = Table.Group(Source, {"Case #"}, {{"Check_PQ", each if List.Contains([Action], "Contain") then "Yes" else "No"}})
in
#"Grouped Rows"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
I utilized the following:
= Table.Group(#"Changed Type2", {"Case #"}, {{"Check", each if List.Contains([Action], "Contain") then "Yes" else "No"}})
But for each case #, it returns "No", where there are some that should have returned "Yes" because "Contain" is in the action.
= Table.Group(#"Changed Type2", {"Case #"}, {{"Check", each if List.Contains(List.Transform([Action], Text.Trim), "Contain") then "Yes" else "No"}})
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
That did not work as well. I should have clarified, but I want to search for "Contain", but the word can be in any part of the row.
= Table.Group(#"Changed Type2", {"Case #"}, {{"Check", each if Text.Contains(Text.Combine([Action], " "), "Contain", Comparer.OrdinalIgnoreCase) then "Yes" else "No"}})
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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 | |
106 | |
105 | |
86 | |
72 |