Cant use a var in count and looking for help,

[sapaulbutler]

Hi,

I am trying to count the number of entries in a survey based on the question Value eg Question 1

Summary Table

Department	Question	Count Responses	Count Agreed
Department A	Question 1	3	2
Department B	Question 1	3	0
Department A	Question 2	3	2

RawData Table

ID	Department	Q1	Q2	Q3
1	Department A	Agree	Agree	Agree
2	Department b	Disagree	Agree	Disagree
3	Department A	Agree	Agree	Disagree

I have tried using variables and count but i get an error that says i need to use a column reference.

Count Responses =

VAR Field = "'RawData'[" & Transformation[Question] & "]"

VAR RowCount = CALCULATE(COUNT(Field))

RETURN

RowCount

Any pointers would be really appreciated.

[sapaulbutler]

Hi @mark_endicott 

Sorry for the misunderstanding, I am trying to count the survey RawData table and populate this into the Summary Table

RawData

I need the summary to count the number of times that questions has been answered by each deparment and also the count of those that agree of strongly agree.  

Does this make it any clearer?  sorry again.

[mark_endicott]

@sapaulbutler - The exact solution I would have provided has been given by @v-yaningy-msft . You should use Power Query first, this will keep your measures down to a minimum and make your data model much more robust and scalable.

Here's a blog I wrote on doing this and why it should be done: https://triangle.im/power-bi-mistake-5-why-pivoted-data-leads-to-power-bi-pain/

[v-yaningy-msft]

Hi, @sapaulbutler 

Thanks for mark_endicott's reply. You can try the following steps to achieve your need.

1. In Power Query

2. In PBI desktop, and use following dax.

agree = 
VAR _counts = CALCULATE(COUNTROWS('Table'),'Table'[Agree?] = "Agree")
RETURN
IF(_counts,_counts,0)

disagree = 
VAR _counts = CALCULATE(COUNTROWS('Table'),'Table'[Agree?] = "Disagree")
RETURN
IF(_counts,_counts,0)

Strongly agree = 
VAR _counts = CALCULATE(COUNTROWS('Table'),'Table'[Agree?] = "Strongly agree")
RETURN
IF(_counts,_counts,0)

Best Regards,
Yang
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

[sapaulbutler]

Hi @mark_endicott 

Unfortunatley that wont work,  if we do the survey a second time but exclude a question then i would only want to count the Question 1 answered not all of the rows, hopefully that makes sense.

Thanks

[mark_endicott]

@sapaulbutler - all of what you are trying to do is possible, but your requirements are not clear.

Are you trying to count the number that answered agree to Q1?

Or are you trying to count the number that answered question 1 in total?

Is the table we need to count the RawData Table?

Or are we counting the Summary table?

-- essentially tell me which table exists in your model. 

[sapaulbutler]

Hi @mark_endicott
Thank you,  sorry another question then

Is it possible to then filter the count based on the question name? for the next column.

I was tying to build a query i could then filter the count for eveyone that answered agree

[mark_endicott]

@sapaulbutler - if you want to count the number of responses from the RawDataTable all you need is:

COUNTROWS( 'RawData Table' )

If this answers your question, please accept as the solution to help with visibility for others with the same challenge.