Forum Discussion
DirectQuery COUNTA
Hello,
can you please give me an advice about DirectQuery functionality?
I have two tables. Table one is called Reports and second one is called Rates. Firstly I didn't need DirectQuery mode, so my solution was following: I created measure in table Rates that was defined like that: x=COUNTA(Reports[id]) and then I created column defined: CumulativeRate=DIVIDE(Rates[sum];Rates[x]). My problem cames up when I wanted to connect my DB as DirectQuery. I am getting an error "COUNTA is not supported in DirectQuery mode".
Can you please help me how can i figure this problem out? Are there an alternative DAX queries for getting same results but in DirectQuery mode?
Hi machovamichaela,
Based on test, it’s not supported to create calculate columns with COUNTA function in “direct query” mode, however you can use this function in a measure, it works on my side.(you should turn on it first)
Turn on the option:
Measure:
Count ID = COUNTA(Table_1[ID])Regards,
Xiaoxin Sheng
3 Replies
Hi machovamichaela,
Based on test, it’s not supported to create calculate columns with COUNTA function in “direct query” mode, however you can use this function in a measure, it works on my side.(you should turn on it first)
Turn on the option:
Measure:
Count ID = COUNTA(Table_1[ID])Regards,
Xiaoxin ShengOK, I'll ask the dumb question, do you need to use COUNTA or can you just use COUNT? COUNT counts numeric values, COUNTA counts numeric, blank and text values.
This link might help, although it is for SQL Server 2016, not sure what SQL you have.
https://msdn.microsoft.com/library/mt723603(SQL.130).aspx
But, that might be one solution, upgrade to SQL Server 2016.
Well I need to use COUNTA function because ID's are strings. I also think that I can't count number of records on the level of DB because I apply some filters in dashboard and I need to know exact number of records that suit this filter. I have to calculate like this thus I need to know an arithmetic mean for every attribute (I use custom visualisation called HorizontalFunnel).
