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,