Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

DISTINCT COUNT for TWO ROWS returns Blank

Hello all, 

 

I am greatful for this forum. Thank you so much and I use it weekly to find new ways of working with DAX. Howver, I am stuck on this one.

 

My first measure is counting members with Auto Insurance Only

Auto Insurance Engagement Count =
    CALCULATE([distinct count of Fullmembershipnumber],AnnualInteractionCount[LOB] = "Auto Insurance",AnnualInteractionCount[Engaged Binary] = "Yes")

 

My second measure is counting members with Home Insurance Only

Home Insurance Engagement Count =
    CALCULATE([distinct count of Fullmembershipnumber],AnnualInteractionCount[LOB] = "Home Insurance",AnnualInteractionCount[Engaged Binary] = "Yes")

 

I have a measure that is counting that have either or.

Home or Auto Insurance Engagement Count =
    CALCULATE([distinct count of Fullmembershipnumber],AnnualInteractionCount[LOB] = "Auto Insurance"||AnnualInteractionCount[LOB] = "Home Insurance" ,AnnualInteractionCount[Engaged Binary] = "Yes")

 

My last measure I want to count where a distinct member has both auto and home insurance. However it is showing blank.

Home and  Auto Insurance Engagement Count =
    CALCULATE([distinct count of Fullmembershipnumber],AnnualInteractionCount[LOB] = "Auto Insurance" && AnnualInteractionCount[LOB] = "Home Insurance" ,AnnualInteractionCount[Engaged Binary] = "Yes")

 

What am I doing wrong?

 

Note: I am not allowed to create calculate columns only measure because of how it is connected to the server. 

 

Kindly

LKtheNoob

 

 

 

 

 

 

Need Help
  • DataInsights's avatar
    DataInsights
    2 years ago

    Anonymous,

     

    Try this measure:

     

    Home or Auto Insurance Engagement Count =
SUMX (
    VALUES ( AnnualInteractionCount[Fullmembershipnumber] ),
    IF (
        CALCULATE (
            COUNTROWS ( AnnualInteractionCount ),
            AnnualInteractionCount[LOB] IN { "Auto Insurance", "Home Insurance" },
            AnnualInteractionCount[Engaged Binary] = "Yes"
        ) = 2,
        1
    )
)

     

4 Replies

  • Data-estDog's avatar
    Data-estDog
    Resolver II
    2 years ago

    AnnualInteractionCount[LOB] will never be more than one value. Its like saying where x  = 1 and x = 0. 
    X can only be occupied by one value. It's either or. You want to filter on the previous counts you made. 

    Home and  Auto Insurance Engagement Count =
    CALCULATE (
        [distinct count of Fullmembershipnumber],
        [Auto Insurance Engagement Count] > 0
            && [Home Insurance Engagement Count] > 0
    )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hello,

     

    Thank you for the quick response. I understand. 

     

    However, I am connected to a 'cube' using Analysis Services. 

     

    I am getting the following error. Any other methods that will work with analysis services?

     

     

    • DataInsights's avatar
      DataInsights
      Super User
      2 years ago

      Anonymous,

       

      Try this measure:

       

      Home or Auto Insurance Engagement Count =
SUMX (
    VALUES ( AnnualInteractionCount[Fullmembershipnumber] ),
    IF (
        CALCULATE (
            COUNTROWS ( AnnualInteractionCount ),
            AnnualInteractionCount[LOB] IN { "Auto Insurance", "Home Insurance" },
            AnnualInteractionCount[Engaged Binary] = "Yes"
        ) = 2,
        1
    )
)

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Thank you so much. It works well!