cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abartozzi Member
Member

Measure to compare values

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. 

 

SNIP.JPG

 

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Seward12533 New Contributor
New Contributor

Re: Measure to compare values

@abartozzi 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. 

3 REPLIES 3
Seward12533 New Contributor
New Contributor

Re: Measure to compare values

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...

Highlighted
abartozzi Member
Member

Re: Measure to compare values

@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 New Contributor
New Contributor

Re: Measure to compare values

@abartozzi 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. 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 42 members 1,076 guests
Please welcome our newest community members: