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

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
 
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"