IF and CASE statement

[dbehara]

Hi All,

Could anyone please help me with below query on power bi, the query im using on tableau

IF NOT ISNULL([task id]) THEN (CASE [activity] WHEN 'Yes' THEN 1 ELSE 0 END) ELSE 0 END

Thanks for help 🙂

[selimovd]

Hey @dbehara ,

what exactly do you want to do? Is that a SQL query? Is that a new calculated column? Should that be a measure?

I think a little bit more context is required to help you.

Best regards

Denis

[dbehara]

@selimovd Im using above query on tableau and trying to re-create same on power bi

basically [task id] should not be null, while calculating activity as 'Yes'

Hope i have answered 🙂

[selimovd]

Hey @dbehara ,

Power BI is not Tableau, what is the reason I asked where exactly you need that.

In this case I can just guess. An approach for a calculated column would be:

New column =
SWITCH(
    TRUE(),
    myTable[task id] = BLANK(), 0,
    myTable[activity] = 'Yes', 1,
    0
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[dbehara]

@selimovd i got the columns now
But 'Yes' getting error (failed to resolve name 'Yes'. it is not a valid table,variable or function name.

[selimovd · Accepted solution]

Hey @dbehara ,

sorry, that was my mistake. You have to use the double quotes.

Try this one:

New column =
SWITCH(
    TRUE(),
    myTable[task id] = BLANK(), 0,
    myTable[activity] = "Yes", 1,
    0
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[JVKR]

We have around 20M leads in the table and facing the issue while pulling the datafrom salesforce(source system) and then applying the above logic.

Earlier the dataflow job was executed in 2hours to fetch the data but after applying the above loic the job is running more than 5hours(timed out error).

Any suggestions to improve the performance here.

Below is the complete steps which we have applied the the table.

let

    Source = Salesforce.Data(SalesforceURL, [ApiVersion=SalesforceApiVersion, CreateNavigationProperties=true]),

    LeadHistory = Source{[Name="LeadHistory"]}[Data],

  #"Removed columns" = Table.RemoveColumns(LeadHistory, {"CreatedBy", "Lead"}),

  #"Changed column type" = Table.TransformColumnTypes(#"Removed columns", {{"OldValue", type text}, {"NewValue", type text}}),

  #"Add column closed date" = Table.AddColumn(

        #"Changed column type",

        "ClosedDate",

        (outer) =>

            List.Min(

                Table.SelectRows(

                    #"Changed column type",

                    (inner) =>

                        inner[LeadId] = outer[LeadId] and inner[NewValue] = "Closed"

                )[CreatedDate]

            ),

        type datetime

    ),

    #"Filtered rows1" = Table.SelectRows(

        #"Add column closed date",

        each [CreatedDate] <= [ClosedDate] or [ClosedDate] = null

    ),

  #"Removed columns 1" = Table.RemoveColumns(#"Filtered rows1", {"ClosedDate"})

in

    #"Removed columns 1"