Forum Discussion

machovamichaela's avatar
machovamichaela
Regular Visitor
10 years ago
Solved

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...
  • Anonymous's avatar
    Anonymous
    10 years ago

    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

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

OK, 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.

machovamichaela's avatar
machovamichaela
Regular Visitor
10 years ago

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).