Forum Discussion

wimsangers's avatar
wimsangers
Helper I
6 years ago
Solved

Measure with multiple conditions

Hi all,

 

I have a problem with creating a measure and need your help.

 

I am trying to get a count of leads that we scored. A lead goes trough a process and when it is passed installation it is considered as a score. There is one flow in the process that is before installation and needs to be completed by the lead. So it is possible that the installation is completed but that the process: complete_data still needs to be completed. This is considered a score. I want to count the leads in which this is the case. 

 

For example this is the case with this lead.  I want to include this lead in my count. This should be based on the following rules:

On the row where the the task_class: "flow.oslo.builder_installation" is, the column "task_finished" has to be filled. When this is the case look at the task_class: "flow.oslo.complete_data", the column task_finished should be filled.

 

So to recap: IF "flow.oslo.builder_installation" "task_finished" <> blank THEN countrows where "flow.oslo.complete_data" "task finished" = blank

 

Thank you in advance.

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi wimsangers ,

    I modified the formula that you can have a try.

    Measure 2 = 
var in_fi = CALCULATETABLE(VALUES(Table1[process_id]),FILTER(Table1,'Table1'[task_class] = "flow.oslo.builder_installation" && 'Table1'[task_finished] <> BLANK()))
var com_bla = CALCULATETABLE(VALUES(Table1[process_id]),FILTER(Table1,'Table1'[task_class] = "flow.oslo.complete_data" && 'Table1'[task_finished] = BLANK()))
return 
COUNTROWS(INTERSECT(in_fi,com_bla))

    In this way, you could show the result in a card visual. If you put it into a table, it will just show in total.

     

    Best Regards,

    Xue Ding

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

4 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    Hi wimsangers ,

    You could try the following formula.

     

    Measure = 
var complete = COUNTROWS(FILTER('Table','Table'[task_class] = "flow.oslo.complete_data" && 'Table'[task_finished] = BLANK()))
var install  = COUNTROWS(FILTER(ALLEXCEPT('Table','Table'[parent_process_id],'Table'[process_id]),'Table'[task_class] = "flow.oslo.builder_installation" && 'Table'[task_finished] <> BLANK()))
return
IF(install<> BLANK(),complete,BLANK())

     

    Best Regards,

    Xue Ding

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

    • wimsangers's avatar
      wimsangers
      Helper I
      6 years ago

      Hi v-xuding-msft ,

       

      Thank you for your reply.

       

      It is working in a way that is shows the leads who have a blank task_finished at the task_class flow.oslo complete data.

      However, it also still counts the leads where flow.oslo.builder_installation have a blank at task_finished.

      See the example.

      • v-xuding-msft's avatar
        v-xuding-msft
        Community Support
        6 years ago

        Hi wimsangers ,

        I modified the formula that you can have a try.

        Measure 2 = 
var in_fi = CALCULATETABLE(VALUES(Table1[process_id]),FILTER(Table1,'Table1'[task_class] = "flow.oslo.builder_installation" && 'Table1'[task_finished] <> BLANK()))
var com_bla = CALCULATETABLE(VALUES(Table1[process_id]),FILTER(Table1,'Table1'[task_class] = "flow.oslo.complete_data" && 'Table1'[task_finished] = BLANK()))
return 
COUNTROWS(INTERSECT(in_fi,com_bla))

        In this way, you could show the result in a card visual. If you put it into a table, it will just show in total.

         

        Best Regards,

        Xue Ding

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

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Can you try creating a column like

    Column Sales Bucket 1 = if(Sales[Age]<25,"A",If(Sales[Sales]<35000,"B","C"))