dax distinctcount measure being affected when using merge queries in query editor
Submitted byAnonymouson06-18-201807:16 AM
I've just come across this issue.
I had a dataset where each row was an application to study on a course. Some of the rows have course fees and some don't. In a separate table I had the average course fee by area, I used the merge queries to bring these two together so that where a course fee is emtpy I can substitute it with the average fee.
However when I use the merge queries my distinctcount measures on my report report the wrong figures. Each time I refresh the dataset, without updating the sql query feeding it and without any data changing, the distinctcount measure would report a different figure.
All values in the column used to join from the applications table were present in the averages table. Moreover, having a measure that just did a straight count on the column reported the right value, and remained correct after each refresh when the distinctcount was changing each time.
I have stripped out the merge queries step in the query editor and will perform this bit of modelling on the SQL side of things, but this is surely an issue if data modelling tools are affecting the accuracy of data output in to report pages?