Forum Discussion

KAmorris's avatar
KAmorris
Frequent Visitor
3 years ago
Solved

Distinct Count with multiple conditions

Hi!    I'm wondering if someone could please help with a suggestion for two (related) issues I'm having. Issue 1:  I'd like to create a measure that counts the number of distinct responses in a...
Need Help
  • KAmorris's avatar
    KAmorris
    3 years ago

    I think I figured it out - I just had to switch the order of the last two statements, so the following seems to work:

    ResponseIDCount = SWITCH(
    TRUE(),
    DISTINCTCOUNT( 'Table'[ResponseID] ) >= 15, DISTINCTCOUNT( ['Table'[ResponseID], DISTINCTCOUNT( 'Table'[ResponseID] ) = 0, 0,
    DISTINCTCOUNT( 'Table'[ResponseID] ) < 15, "<15")

  • rsbin's avatar
    rsbin
    3 years ago

    KAmorris ,

    You are close

    SWITCH(
    TRUE(),
    Calculate(DISTINCTCOUNT(Table[Response_ID]), Table[Column]=1) >=15, DISTINCTCOUNT(Table[Response_ID]), Table[Column]=1) 
    // If Calculation is >= 15, then use the Calculation amount
    Calculate(DISTINCTCOUNT(Table[Response_ID]), Table[Column]=1) =0,0,
    // If Calculation = 0, then 0.
    Calculate(DISTINCTCOUNT(Table[Response_ID]), Table[Column]=1) <15, "<15")
    // If Calculation < 15, then use "<15".   In Dax you can use "//" to make comments to yourself or others.
     
    Hope this added clarification helps.
     
rsbin's avatar
rsbin
Community Champion
3 years ago

KAmorris ,

The SWITCH function is similar to a nested IF statement.  Please try this:

ResponseIDCount = SWITCH(
                      TRUE(),
           DISTINCTCOUNT( 'Table'[ResponseID] ) >= 15, DISTINCTCOUNT( ['Table'[ResponseID] ),
           DISTINCTCOUNT( 'Table'[ResponseID] ) < 15, "<15",
           DISTINCTCOUNT( 'Table'[ResponseID] ) = 0, 0 )

Regards,

KAmorris's avatar
KAmorris
Frequent Visitor
3 years ago

Thanks so much rsbin . Your suggestion seems to mostly work, but for some reason the count when the distinct count of 'Table'[ResponseID] should be 0 still shows up as "<15". Any other ideas or things I should check for?

  • KAmorris's avatar
    KAmorris
    Frequent Visitor
    3 years ago

    I think I figured it out - I just had to switch the order of the last two statements, so the following seems to work:

    ResponseIDCount = SWITCH(
    TRUE(),
    DISTINCTCOUNT( 'Table'[ResponseID] ) >= 15, DISTINCTCOUNT( ['Table'[ResponseID], DISTINCTCOUNT( 'Table'[ResponseID] ) = 0, 0,
    DISTINCTCOUNT( 'Table'[ResponseID] ) < 15, "<15")

    • rsbin's avatar
      rsbin
      Community Champion
      3 years ago

      KAmorris ,

      Glad you were able to get it to work for you.

      Regards,

      • KAmorris's avatar
        KAmorris
        Frequent Visitor
        3 years ago

        If you don't mind, I have one follow up question rsbin.  Is there a way to use a similar solution to solve this issue: 

        I'm trying to count the number of users ('Table'[Response_ID]) who had a particular response to another column ('Table'[Column]). Responses were to a simple yes/no question, so I transformed it so that a yes = 1, and a no was a blank (null).

        What I'd like is to have a measure that essentially counts the number of distinct responses who said yes. But this measure should only return the following:

        - the count if the number of responses is greater than or equal to 15.

        - If the number of responses is 0, then return the number '0'.

        - If the number of responses is between 1 and 14, then return "<15". 

        I had been using the measure: CALCULATE (DISTINCTCOUNT('Table'[Response_ID]), 'Table'[Column] =1)

        But this doesn't account for the nuances as outlined above (e.g., if the # of responses <15 or 0). 

        Would a similar SWITCH statement be useful here? I'm trying to play around with it but haven't managed to make it work yet... 

        Thanks so very much for all of your help!!