Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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

View solution in original post

3 REPLIES 3
Seward12533
Solution Sage
Solution Sage

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
Not applicable

@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

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.