Compare values in table

[ChenChristyYu]

Hi expert,

Here is what I am trying to do. I want to compare the value of column 'Synced to BRPrint' in Table 'Reportxx' and the value of column 'Sync from Reportxx' in Table 'BRPrintxx'. I was thinking about creating a measure but I have no clue how to write the DAX yet. Can someone help on this? Really appreciate it.

[Phil_Seamark]

Hi @ChenChristyYu

When you say you want to compare?  What do you mean by that?

[ChenChristyYu]

If the value does not match then I want to point out the difference on my dashboard, I thought about highlighting the value that does not match, but it does not seem like PowerBI has the function. Or I can create a measure and print out"'value A' and 'Value B' do not match"

[Phil_Seamark]

So in the top line "Australia", the value in the highlighted columns match?  Whereas for the 2nd row "Austria" the columns don't match.

Is the country code unique in both tables?  Or can "Austrialia" be on more than one row in either table?

[ChenChristyYu]

Yes, the value of Australia match, but the one of Austria does not. the country code is unique in both tables. Each country only appears one time in each table.

[Phil_Seamark]

Hi @ChenChristyYu

If the country column is unqiue in both tables, you can create relationship between the two tables using the Country column

Then in one of the tables you can add a calculated colum that uses logic to compare the two.

The calculation below is one you might add to Table1.

Difference Column = 
    RELATED('Table2'[Value]) - 'Table1'[Value]

I have a simple PBIX file here that shows the technique

https://1drv.ms/u/s!AtDlC2rep7a-oxHf9-K0gats1V2x

[ChenChristyYu]

Hi Phil,

Thanks for your demo. It has been helpful. I tried your method, but I am having some issue getting it work in my situation. I am creating the difference variable in BRPrintxx table. I wonder if it is because of the Country table in the middle. Please let me know if you have any idea. Thanks a lot. 

[v-xjiin-msft]

Hi @ChenChristyYu,

Generally even there's a Country table in the middle of the relationship. It will not affect the result.

Then based on your screenshot, it seems like you are using a measure. You should know that we cannot call a column directly in a measure. So please try calculated column instead of using measure.

By the way if the country code is unique in both tables. Why don't you just create a relationship between the two tables?

Thanks,
Xi Jin.

[Ashish_Mathur]

Hi,

At the back end i.e. the Data Mode, you can bring over the numeric column from the other table and then compare.  To get more specific help, share your data.

[ChenChristyYu]

Hi Ashish,

I am doing this for my corp, so it is not very convenient to share my data. Can you maybe point me to where the function is at or any article or video demonstrate this function? 

Thanks very much.

best,

Chen

[ChenChristyYu]

I mean if the number equal to each other.