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.
Hello all,
I am new to Power BI and need some help please.
I'm looking for a DAX calculation to help me identify where a marker hasn't been added to a case. See table below where I have my columns Case ID, Case category and Marker. In this example the case categories Performance and Contract should also have a Marker but they don't. My Case category Pay doesn't need a marker.
What I think I want is a new column that looks to see if there is a marker, if there isn't it looks to see what the case category is, then based on that information it creates a flag in a new column to identify a case category that should have a flag doesn't have one.
I hope that makes sense. Any help would be much appreciated.
Regards,
Emily.
Solved! Go to Solution.
Hi @EmilyM2019 ,
Go to Power Query, go to Add Column tab, Conditional Column and add along the lines below. You can put in multiple statements.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hi @EmilyM2019 ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You could also do an added column in the modeling tab (not in Power Query) using DAX like this:
New Marker =
IF(
OR(TableName[CaseCategory] = "Performance", TableName[CaseCategory] = "Contract") = TRUE,
"Yes",
TableName[Marker])
This simply checks to see if the Case Category is one of the two values required. If so, it returns "Yes". If not, it defaults to whatever the current Marker is.
If you want to do it in the Query Editor, you can do as Nathaniel suggested and then merge the columns to get a single value.
Hi @JonV ,
However, less of a performance hit when you use Power Query over a calculated column.
Nathaniel
Proud to be a Super User!
Hi @EmilyM2019 ,
Go to Power Query, go to Add Column tab, Conditional Column and add along the lines below. You can put in multiple statements.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
I ended up using the conditional column solution to identify the need for a marker.
I then did nested IF to identify where there should be a marker but there wasn't one.
Thanks for all the help.
@Nathaniel_C Thanks I will give this a go. Although the fact that I can't even post a table into this message thread doesn't fill me with much hope 😉
Hi @EmilyM2019 ,
Try copy and paste. Below I copied this table from Power BI and pasted here. It makes it easy for your forum helpers to copy and paste into their enter data in Power BI.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
OrderMetric AHighest Metric Same Order
beta 42 | 10 | 12 |
beta 42 | 12 | 12 |
beta 71 | 11 | 17 |
beta 71 | 17 | 17 |
Delta 101 | 21 | 21 |
Delta 101 | 5 | 21 |
Delta 101 | 6 | 21 |
Alpha 102 | 10 | 15 |
Alpha 102 | 12 | 15 |
Alpha 102 | 15 | 15 |
Alpha 102 | 3 | 15 |
Alpha 102 | 7 | 15 |
ETA 52 | 10 | 14 |
ETA 52 | 12 | 14 |
ETA 52 | 14 | 14 |
ETA 52 | 5 | 14 |
ETA 52 | 6 | 14 |
Proud to be a Super User!
Case ID Case category Marker
1 Sickness Yes
2 Pay
3 Performance
4 Performance
5 Time off Yes
6 Pay
7 Retirement Yes
8 Contract
Hi @EmilyM2019
Try this:
Your table worked fine!
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
@Nathaniel_CI've literally tried everything and it won't let me paste in a table from Power BI (or excel). So apologies for my amaturish "table".
I've followed your instructions to create the new conditional column but I can't figure out how to put a marker in against one case category and not another.
Hi @EmilyM2019 ,
Use the Custom Colmun in Power Query Editor is suitable in this scenario, but @Nathaniel_C 's solution seems need add every catogory, so we can add a custom column using following folmula:
if [Marker]&"" <> "Yes" and [Case category] <> "Pay" then "Yes" else null
The full M query is here:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOTM7OSy0uBjIjU4uVYnWilYyA7IDESiAJ5hqDuKlFaflFuYl5yakwYRPswqZARkhmbqpCfloakplmqGaaAxlBqSWZRam5qXklSOosgGzn/LySosTkErDiWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case ID" = _t, #"Case category" = _t, Marker = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Case ID", Int64.Type}, {"Case category", type text}, {"Marker", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "NeedMarker", each if [Marker]&"" <> "Yes" and [Case category] <> "Pay" then "Yes" else null)
in
#"Added Custom"
If you want the calculated column, we can also create one use the following DAX formula:
NeedMarkerDax =
IF ( AND ( [Marker] & "" <> "Yes", [Case category] <> "Pay" ), "Yes", "" )
OR
NeedMarkerDax =
IF ( AND ( [Marker] & "" = "", [Case category] <> "Pay" ), "Yes", "" )
If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Need the table, please.
Proud to be a Super User!
Trying to post it 🙂 🙂
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |