topic Re: Cant use a var in count and looking for help, in DAX Commands and Tips

Cant use a var in count and looking for help,

sapaulbutler — Thu, 19 Dec 2024 15:33:42 GMT

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.

Re: Cant use a var in count and looking for help,

mark_endicott — Thu, 19 Dec 2024 15:43:09 GMT

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.

Re: Cant use a var in count and looking for help,

sapaulbutler — Thu, 19 Dec 2024 15:49:20 GMT

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

Re: Cant use a var in count and looking for help,

sapaulbutler — Thu, 19 Dec 2024 16:04:06 GMT

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

Re: Cant use a var in count and looking for help,

mark_endicott — Thu, 19 Dec 2024 16:31:00 GMT

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.

Re: Cant use a var in count and looking for help,

sapaulbutler — Thu, 19 Dec 2024 17:23:39 GMT

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.

Re: Cant use a var in count and looking for help,

Anonymous — Fri, 20 Dec 2024 07:10:09 GMT

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

Re: Cant use a var in count and looking for help,

mark_endicott — Fri, 20 Dec 2024 13:32:04 GMT

sapaulbutler - The exact solution I would have provided has been given by Anonymous . 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/

Re: Cant use a var in count and looking for help,

mark_endicott — Mon, 23 Dec 2024 10:54:51 GMT

sapaulbutler - did we resolve your issue? If we did, please select the appropriate solution, it helps with visibility for others and for SuperUsers to keep their status! Thanks!