Forum Discussion

dbehara's avatar
dbehara
Frequent Visitor
4 years ago
Solved

IF and CASE statement

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   Tha...
Need Help
  • selimovd's avatar
    selimovd
    4 years ago

    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
     
dbehara's avatar
dbehara
Frequent Visitor
4 years ago

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's avatar
selimovd
Most Valuable Professional
4 years ago

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
 
  • JVKR's avatar
    JVKR
    Frequent Visitor
    2 years ago

    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"