cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Compare values in table

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.

 

Table Structure.PNG

10 REPLIES 10
Microsoft
Microsoft

Hi @ChenChristyYu

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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"

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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. 

 

Difference error.PNGRelationship 1.PNGRelationship 2.PNG

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.

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

I mean if the number equal to each other.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors