DAX calulation help for identifying blank values

[EmilyM2019]

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.

[v-lid-msft]

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.

[JonV]

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.

[Nathaniel_C]

Hi @JonV ,

However, less of a performance hit when you use Power Query over a calculated column.

Nathaniel

[Nathaniel_C · Accepted 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

[EmilyM2019]

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.

[EmilyM2019]

@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 😉 

[Nathaniel_C]

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

[EmilyM2019]

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

[Nathaniel_C]

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

[EmilyM2019]

@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.

[v-lid-msft]

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.

[Nathaniel_C]

Need the table, please.

[EmilyM2019]

Trying to post it 🙂 🙂