Forum Discussion

xiumi_hou's avatar
xiumi_hou
Icon for Post Partisan rankPost Partisan
6 years ago
Solved

urgent: Quick question for dax

Dear all,

 

I have two measures.  I want to count the number of [measure 1] that the measure 2's result is 0

 

When I use measure 1 as a card view and put measure 2 to a filter(set=0) which works fine.

However when I want to combine those two into one measure, it give wrong result(blank) as below. Can anyone help? 

SF_Before_A1 = calculate([SF_case_inactive],FILTER('Calls','Calls'[Count_Held_Sessions_A1]=0))
 
 

Measure 1:

SF_case_inactive = calculate([Count_SF_Cases],USERELATIONSHIP('Date'[Date],'Cases'[Cases_cstm.closed_date_c]))
 

Measure 2: 

Count_Held_Sessions_A1 =
CALCULATE(
COUNT(Calls[Parent_id])+0,
FILTER(
Calls,
Calls[status]="Held"&&
Calls[Type_c]="Assessment"
 
))
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi xiumi_hou ,

     

    Yes,my measure is based on yours,have you tried it?

    Your measure "SF_Before_A1" should be corrected to either of the following ones:

     

    SF_Before_A1 = calculate([SF_case_inactive], 'Calls'[Count_Held_Sessions_A1]=0)

     

    Or

     

    SF_Before_A1 = If ('Calls'[Count_Held_Sessions_A1]=0,[SF_case_inactive],blank())

     

    I just advise you not to use "filter" funtion in this situation.

    And measure 1 and measure 2 remain the same.

    Pls try it and advise me if there's any error returned.

     

    Best Regards,

    Kelly

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    When you filter Measure1 using Measure2 you need to data grouping at some level to filter

    Like

    Sumx(filter(values(table[id],measure2 >0),measure1)

Sumx(filter(summarize(table[id],"M1",sum(measure2),"M2",sum(measure1)),[M2]>0),[M1])

     

     

    • xiumi_hou's avatar
      xiumi_hou
      Icon for Post Partisan rankPost Partisan
      6 years ago

      Can you help me revise mine? Thank you so much

      • v-kelly-msft's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi xiumi_hou ,

         

        Yes,my measure is based on yours,have you tried it?

        Your measure "SF_Before_A1" should be corrected to either of the following ones:

         

        SF_Before_A1 = calculate([SF_case_inactive], 'Calls'[Count_Held_Sessions_A1]=0)

         

        Or

         

        SF_Before_A1 = If ('Calls'[Count_Held_Sessions_A1]=0,[SF_case_inactive],blank())

         

        I just advise you not to use "filter" funtion in this situation.

        And measure 1 and measure 2 remain the same.

        Pls try it and advise me if there's any error returned.

         

        Best Regards,

        Kelly

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi xiumi_hou ，

     

    Measure “SF_Before_A1” should be corrected as below:

     

     

    SF_Before_A1 = calculate([SF_case_inactive], [Count_Held_Sessions_A1]=0)

     

    Or  

     

    SF_Before_A1 = If ([Count_Held_Sessions_A1]=0,[SF_case_inactive],blank())

     

     

     

    As you’re calculating a result based on measure 1,and measure 1 is based on table “Cases”, if you wanna use filter function, it should be within table “Cases”.

     

    If it doesn’t work, pls show me your sample data.

     

    Hope this would help.

     

    Best Regards,

    Kelly