distinctcount value changing on data refresh without query change

[Anonymous]

Hi all,

hoping for a possible explanation on the following scenario.

I have data that is loaded in to a power bi report using import via a sql query. One of the measures I have set up is a distinctcount of one of the columns. Every time I hit refresh data, the value of this measure changes without any change being made to the query that feeds it. I can re-run the query in SSMS and the numbers don't change so the data definitely isn't changing between refreshes.

When I use a normal count function on the same column, the overall number stays the same (and matches the query when run in SSMS).

Is there something I don't know about with the distinctcount function that is affecting how it's calculated?

I have tried a number of things including removing any relationship links in the data model so it is just counted without any possible filtering or restrictions, changing the data type of the column(it is currently whole number), but I'm getting the same behaviour. 

I'm really stumped on this, so any suggestions or previous experience would be a great help!

Thanks.

[Anonymous · Accepted solution]

OK, so just in case anyone comes across this in the future I think I have it figured.

The table of data I was using had a merge queries step applied to it after import. It seems as though this mergnig of queries was the issue. I took this step out of the query editor and the issue has disappeared.

Seems a bit ridiculous that utilising the modelling tools would impact on the accuracy of dax that is then applied to the updated dataset. Fix for now would seem to be handling more of the modelling on the SQL side (yes I know this should be preferred anyway).

What an afternoon!