Forum Discussion

anna_zuckers's avatar
anna_zuckers
New Member
3 years ago
Solved

How to convert Case function to Dax

I have a formula that I used in Datastudio but I cannot figure out how to convert it to Dax:

 

Formula #1:
CASE
WHEN Word Count> 4 THEN "Long tail"
ELSE "Short tail"
END
 
Formula #2:
CASE
WHEN Avg position >=0 AND Avg position <= 3 THEN "Hyper traffic (1-3) "
WHEN Avg position >3 AND Avg position <= 10 THEN "Traffic (4-10) "
WHEN Avg position >10 AND Avg position <= 20 THEN "Striking Distance (11-20)"
WHEN Avg position > 20 THEN "Did not Rank"
ELSE "No Rank"
END
 
Formula #3: (if query contains "omega", then replace the query to "omega")
CASE
WHEN REGEXP_MATCH(query, '.*omega.*') THEN "Omega"
WHEN REGEXP_MATCH(query, '.*hublot.*') THEN "Hublot"
WHEN REGEXP_MATCH(query, '.*invicta.*') THEN "Invicta"
ELSE "Others"
END
 
I would appreciate any help.
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  anna_zuckers ,

    Here are the steps you can follow：

    Create calculated column.

    Formula #1:

    Formula #1=
IF(
    [Word Count] >4 ,"Long tail","Short tail")

    Formula #2:

    Formula #2 =
SWITCH(
    TRUE(),
    [Avg position] >=0&&[Avg position]<=3,"Hyper traffic (1-3)",
    [Avg position] >3&&[Avg position]<=10,"Traffic (4-10)",
    [Avg position] >10&&[Avg position]<=20,"Striking Distance (11-20)",
    [Avg position]>20,"Did not Rank",
    "No Rank")

    Formula #3:

    Formula #3 =
SWITCH(
    TRUE(),
    CONTAINSSTRING('Table3'[query],".*omega.*")=TRUE(),
SUBSTITUTE(
    [query],".*omega.*","Omega"),
CONTAINSSTRING('Table3'[query],".*hublot.*")=TRUE(),
   SUBSTITUTE(
    [query],".*hublot.*","Hublo"),
CONTAINSSTRING('Table3'[query],".*invicta.*")=TRUE(),
     SUBSTITUTE(
    [query],".*invicta.*","Invicta"),
    "Others")

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  anna_zuckers ,

    Here are the steps you can follow：

    Create calculated column.

    Formula #1:

    Formula #1=
IF(
    [Word Count] >4 ,"Long tail","Short tail")

    Formula #2:

    Formula #2 =
SWITCH(
    TRUE(),
    [Avg position] >=0&&[Avg position]<=3,"Hyper traffic (1-3)",
    [Avg position] >3&&[Avg position]<=10,"Traffic (4-10)",
    [Avg position] >10&&[Avg position]<=20,"Striking Distance (11-20)",
    [Avg position]>20,"Did not Rank",
    "No Rank")

    Formula #3:

    Formula #3 =
SWITCH(
    TRUE(),
    CONTAINSSTRING('Table3'[query],".*omega.*")=TRUE(),
SUBSTITUTE(
    [query],".*omega.*","Omega"),
CONTAINSSTRING('Table3'[query],".*hublot.*")=TRUE(),
   SUBSTITUTE(
    [query],".*hublot.*","Hublo"),
CONTAINSSTRING('Table3'[query],".*invicta.*")=TRUE(),
     SUBSTITUTE(
    [query],".*invicta.*","Invicta"),
    "Others")

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly