Can you change data format in direct query?

[Anonymous]

i am tyring to change the data format in direct query but unable to do so how do i go about changing something to a text data format without being able to create calculated columns or use the data format part?

[amitchandak]

@Anonymous , I was able to change the data type from number to text.

refer : https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intelligence-in/ba-p/922885

I changed the data type of year

[Anonymous]

@amitchandak & @Greg_Deckler  i do not have an opption to change the data type or format. Its not something i can edit it is currently set to teext for the field that is not working please see example below;

[Greg_Deckler]

Also, you may be able to change the data type in the Query Editor for DirectQuery. You can't in Desktop when not in the Query Editor but you may be able to change the data type while in the Query Editor. But still weird that you are getting that error with COUNT.

[Anonymous]

@Greg_Deckler no can't use the query editor doesnt give me the option as is analysis data. I tried the COUNTX but get the same error not sure where to go with this now

[Greg_Deckler]

Also, if you really think it is a bug, what version of Desktop are you using? You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

[Greg_Deckler]

You don't have any other column that you can try counting? What about instead of COUNTX, just use COUNTROWS in my formula I posted?

[Greg_Deckler]

COUNTROWS((FILTER(ALLEXCEPT(table1,table1[country),table1[SalesUnits]>0))

[Anonymous]

Thanks @Greg_Deckler  but i may have solved this it seems it could be down to the relationships in the tabular model as have managed to get the COUNTA to work when using 1 table but when looking at multiple it breaks again

[Greg_Deckler]

Still weird! 🙂

[Greg_Deckler]

Also, this is very odd, COUNT specifically says it supports Strings.

https://docs.microsoft.com/en-us/dax/count-function-dax

[Anonymous]

@Greg_Deckler yeah that is what i am really struggling with as it says it should work with it

[Greg_Deckler]

No, love the user name.

[Anonymous]

@Greg_Deckler thank you so how would i fix the following calculation;

CALCULATE(COUNT(Product[Name]),FILTER(table1,table1[SalesUnits]>0),ALLEXCEPT(table1,table1[country]))

 

as currently it is saying i am unable to do this;

[Greg_Deckler]

I think COUNTA ?

[Anonymous]

@Greg_Deckler have tried counta but that doesnt work either as when i put into a table the total of the table is not correct it comes out about 2000 when should be about 100,000

[Greg_Deckler]

Maybe try a different way of doing that Measure:

CALCULATE(COUNT(Product[Name]),FILTER(table1,table1[SalesUnits]>0),ALLEXCEPT(table1,table1[country]))

instead try:

COUNTX(FILTER(ALLEXCEPT(table1,table1[country),table1[SalesUnits]>0),Product[Name])