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 ,

Please post a small sample of data.  Paste it in as a table not as an image, but exclude any sensitive data.  Enables folks to easily use that data to come up with a workable solution.

Edit:  I re-read your question above a couple of times.

Yes, the SWITCH would work as well.

Instead of:

DISTINCTCOUNT( 'Table'[ResponseID] ) in the Switch statement

use your Measure above:

CALCULATE (DISTINCTCOUNT('Table'[Response_ID]), 'Table'[Column] =1)

Replace [Column] with [MoreThanOneDisability].

I noticed when you pasted your data, there were extra spaces.  Be sure your Column Names are clean (no extra spaces before or after).

 

KAmorris's avatar
KAmorris
Frequent Visitor
3 years ago

Sorry about the extra spaces - I don't have extra spaces in my column names in my data, but good reminder! 

I've tried to replace as you suggested, but not sure if I correctly understand. Should it be something like the following? If so, the result I get is quite high compared to what it should be.


SWITCH(
TRUE(),
Calculate(DISTINCTCOUNT(Table[Response_ID]), Table[Column]=1) >=15, DISTINCTCOUNT(Table[Response_ID]),
Calculate(DISTINCTCOUNT(Table[Response_ID]), Table[Column]=1) =0,0,
Calculate(DISTINCTCOUNT(Table[Response_ID]), Table[Column]=1) <15, "<15")
  • rsbin's avatar
    rsbin
    Community Champion
    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.
     
  • KAmorris's avatar
    KAmorris
    Frequent Visitor
    3 years ago

    Yes! That's it!! Thank you so so much rsbin. This has been a huge struggle for me and I really appreciate your willingness to work it through with me. Thank you!!