Measure to compare values

[Anonymous]

Hi Community, 

I need your help to sort this out please.

So I have this chart made up of fields from different tables. Some fields are in a relationship, some others are just unrelated. 

what I need to do is setting up a measure that checks if the first AdvPur column (which comes from a table) matches with the second AdvPur column(that comes from another table). 

The ideal is having an indicator that can spot all the discrepancies. 

Thank you for your suggestions.

alberto

[Seward12533]

You will be much better off doing this in a visual than a calcualted column as any filters won't apply to the table as its only calcuated once (BEFORE) you get a chance ot set filters. As long as the two tables are related its simple I do this all the time for variance analysis between diffent sources.

Write two measures for [AdvPurch Source A] and [Adv Purch Source B] and antoher measure for the difference between them Delta = [AdvPurch Source A]-[AdvPurch Source B]

Build a visual with at least one Parameter from one of your bidge tables (i.e. table[VA Kg Cls] but it can be multiple and display your two measures and the delta and then filter the visual to exclude any results where Delta is Blank or Zero. 

But if you really want to do it your way I add a calcualted column that subdraces the two AdvPurch columns and then filter out the zeros.

A final tip - this kind of analysis is much easier in PowerPivot in excel vs PowerBI the data models are the same and its easy to do lille side calcs etc...

[Anonymous]

@Seward12533

read carefully:

"what I need to do is setting up a measure that checks..."

I don't need to create a delta betwenn column 1 and 2, I need to spot differencies, and eventualy it could become a 1/0 field. 

Even because advpur is formatted as 60D,7D,3D so it's a text field. 

I also know the theory behind this, I sought help to code it, since i'm trying but I'm failing

[Seward12533 · Accepted solution]

@Anonymous Apoliges I misread it but I'm guess still not 100% sure what your after.

If you getting errors that say you can't do T/F comparison do an if and return "Yes" or "No" which DAX will let you use in a filter statment like CALCULATE([measure],FILTER(table,[ADV Purch Check]="Yes"))

If you are building a visual that will setup the filter context such that you would expect a single value of each of the [ADVPurch] values then try ADVPurch1 = FIRSTNONBLANK(table1[ADVPurch]) and ADVPurch2 = FIRSTNONBLANK(table2[ADVPurch] this will allow you to extract the single value since measures require aggregate functions. Which you can then write a Mesure for ADVPurch Check that checks to see if they are equal and filter out all results that are true in the visual filter. 

If this isn't what you are after please post more details about your model or ideally a PowerBI file withe some representative data adn your current best efforts along with desired output.