Calculated Column Discrepancy in Power BI - Concatenated Keys Issue

[achuri]

Hello,

I have encountered an issue with a calculated column in Power BI. I have two tables, each containing a "CombinedKey" column formed by concatenating five specific columns. I've successfully created similar calculated columns before, which yield the expected results. However, this particular calculated column is producing the opposite results. Instead of giving out "False" it is giving "True" for all the values in both the tables.

Combined Key:

CombinedKey1 = COMBINEVALUES("-",Table1[Col1], Table1[Col2], Table1[Col3] ,Table1[Col4] , Table1[Col5])

CombinedKey2 = COMBINEVALUES("-",Table2[Col1], Table2[Col2], Table2[Col3] ,Table2[Col4] , Table2[Col5])

Calculated Column:

Missing_Size =
Var CombKeyTable1 = VALUES(Table1[CombinedKey1])
VAR CombKeyTable2 = VALUES(Table2[CombinedKey2])

RETURN
IF(
COUNTROWS(
EXCEPT(
CombKeyTable1, CombKeyTable2)) > 0,
"True", -- True, stating ComKeyTable1 has a new value which is not present in CombKeyTable2
"False")

I suspect the issue may stem from the concatenation process or the comparison of these concatenated keys across the two tables. To troubleshoot, I extracted the "CombinedKey" values from both tables and compared them in Excel for any discrepancies such as whitespace or formatting issues. Surprisingly, they matched perfectly in Excel.

Could anyone please advise on how I can effectively approach and resolve this discrepancy in Power BI

Thank you!

[Anonymous · Accepted solution]

Hi @achuri,

Per my test, I could not reproduce your issue by following your steps.

To troubleshoot your issue, please create a new calculated column to check if we can output values that are not the same between CombinedKey1 and CombinedKey2 (if any):

outputdifference =

Var CombKeyTable1 = VALUES(Table1[CombinedKey1])

VAR CombKeyTable2 = VALUES(Table2[CombinedKey2])

RETURN

EXCEPT(

CombKeyTable1, CombKeyTable2)

Also, make sure that Power BI Desktop is up to date and also create a new report to check if the issue still exists.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[achuri]

Thank you so much!! This approach was helpful. This helped me debug my issue and find the root cause of it.
Basically my Table1 holds mulitple values of Combined Key and Table2 was like a master data, so only unique values of Combined Key. My aim was to find missing or new Combined values from table 1 which are not present in Table 2.
More like a VLOOKUP function but in DAX, so I used Filter function along with Count Rows function and it works.

But thanks to you for helping me debug and guiding me with regards to the outputdifference table, that helped me understand my problem and tell me what I was missing!