Merged Query Calcuation

[terpsbmore]

Hello All,

I recently appended and merged several queries to aggregate the data that I need in a single query table. My issue is the new measures I created for the merged query are not correct. So here is what I did.

All data is excel based. I appended 2 excel files for years 2016 & 2017 because one of the files reached the row limit for excel and I need both excel files together show 2 years of data. The newly appended file has every record spanning the 2 years and each has a unique id. That worked just fine.

Next, I merged (Left Outer Join) a third excel query containing a subset of unique id's to the appended query in order to add a new column which corresponds to the unique id column of the appended query. The resulting query having all unique id's for 2 years and a subset of the unique ids.

Finally, I am using a measure on the second (merged) unique id column to count the unqiue id's however, my totals do not match what is in excel.

UniqeID Count = DISTINCTCOUNT('TableName'[Merged Unique ID Column])

Any help or advice is much appreciated.

[v-chuncz-msft]

@terpsbmore,

A left outer join specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.

[terpsbmore]

I used a full join with the same result, any suggestions?

[v-chuncz-msft · Accepted solution]

@terpsbmore,

Alternatively, you may try to use LOOKUPVALUE Function to add a calculated column in DAX.

[terpsbmore]

Not sure why I didn't think of that. Geez....Thank you!