Count of times Y is selected FOR EACH client id

[SHADOWFAX]

Have some survey data that I need help figuring out for each unique respondent, the number of respondents that select Y once, selected Y twice, selcted Y 3 times and so on. My data is setup as follow.

So in the above example, there would be a total of 2 unique respodents that answered Y to 3 questions, 1 unique respodent that answered Y to 2 questions, and 2 unique respodents that selected Y to 1 question.

Need to be able to graph the results in a bar chat by the frequency of Y answers for each unqiue client id.

Below is the same data setup in a color scheme by Client ID to easier viewing.

Thanks in advance

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[sturlaws]

Hi, , 

first create a frequency table like this:

Frequencies = GENERATESERIES(0,10,1)

 and rename the column [Value] to [Frequency]. This table should have no relationship to any other table.

 

Next create this measure:
Frequency Y answers = VAR _frequency = SELECTEDVALUE ( Frequencies[Frequency] ) VAR _CountOfYesPerClient = ADDCOLUMNS ( VALUES ( Survey[Client ID] ), "count", CALCULATE ( COUNTROWS ( Survey ), Survey[Answer] = "Y" ) ) VAR _frequencyCount = COUNTROWS ( FILTER ( _CountOfYesPerClient, [count] = _frequency ) ) RETURN _frequencyCount You should now be able to create this chart: 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[SHADOWFAX]

Getting the following error when trying to create the second measure VAR_CountOfYesPerClient

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"

Here is the full measure with the error

Thanks for your help!

[sturlaws]

It seems like you are pulling in two different tables in your measure, I have no way of knowing how they are related.

It would be helpful if you could share your pbix, or if that is not possible, create an example file reproducing your model.

[v-cgao-msft]

Hi @SHADOWFAX ,

Please try like:

CountOfYesPerClient =
ADDCOLUMNS (
    VALUES ( 'SDOH Survey Data'[ID] ),
    "count", CALCULATE ( COUNTROWS ( 'SDOH Survey Data' ), 'SDOH Survey Data'[Answer] = "Y" )
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[sturlaws]

sorry about the formatting, the forum is acting up all weird

[Ahmedx]

look it up you need it
https://dropmefiles.com/fSd7Y