Forum Discussion
Compare two columns' values
Hello Power BI Gods!
I'm interested in comparing the values of two different columns in the same Table.
My desired outcome looks like this
I would like to compare Allocation Requested with Allocation actual and if the following logic is True I will get store the results as shown in a new column or measure , just as shown here.
Note: The name of my table is called Task List, I am not sure if the space between task and list plays a huge role in the syntax or not.
Basic logic is:
Allocation requested = allocation actual - Allocation match
Allocation requested < allocation actual - Over allocated
Allocation requested > allocation actual - requested too high
Currently, the best I can get is this:
by using the following formula:
Any input is appreciated, I'd like to start a discussion 🙂
Anonymous if these are columns, add the new column using the following expression, based on your expression, looks like these are columns.
New Column = SWITCH ( TRUE(), Table[Allocation Requested] = Table[Allocation Actual], "Allocation Match", Table[Allocation Requested] < Table[Allocation Actual], "Over allocated", "Requested too high" )Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
13 Replies
Anonymous if these are columns, add the new column using the following expression, based on your expression, looks like these are columns.
New Column = SWITCH ( TRUE(), Table[Allocation Requested] = Table[Allocation Actual], "Allocation Match", Table[Allocation Requested] < Table[Allocation Actual], "Over allocated", "Requested too high" )Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
As per your advice. I managed to do it both as a new column and as measure. I have provided the exact code i used respectively:
Column solution
New Column =SWITCH ( TRUE(),'Task List'[Allocation Requested]= 'Task List'[Allocation Actual], "Allocation Match",'Task List'[Allocation Requested]< 'Task List'[Allocation Actual], "Over allocated","Requested too high")And this worked as a measure:Allocation =SWITCH ( TRUE(),SUM('Task List'[Allocation Requested]) = SUM('Task List'[Allocation Actual]), "Allocation Match",SUM('Task List'[Allocation Requested]) < SUM('Task List'[Allocation Actual]), "Over allocated","Requested too high")
Anonymous allocation request and allocation actual, are these columns or measure?
Those are columns, my idea is to create a table in my report which show the values from those two column and include a third clumn in that table, which will show whether there is an allocation match, over allocation or requested too high. Just like the second screen shot in the post (allocation is a measure there).
Anonymous just sent the solution, add as a column.
Anonymous if you want this as a measure, just use the same expression, add a new measure, wrap both the columns in SUM and it should work.
Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Anonymous my apologies, there should be a comma after TRUE(), sorry about that.
I am trying to determine if a value appears in a column in two different tables...for example
Device Names in table one are
ABC123
DEF456
Device Name in table two are
ABC123
GHK789
So the ABC123 would produce a positive result of some sort, don't really care what
Whereas DEF456 and GHK789 would BOTH produce negative results for investigation.
The idea being to create some sort of action table to enable us to understand gaps in our data in various systems where the data should exist.
Hi,
In TableA, write this calculated column formula
Present in TableB = lookupvalue('tableb'[Code],'tableb'[Code],'tablea'[Code])
In TableB, write this calculated column formula
Present in TableA = lookupvalue('tablea'[Code],'tablea'[Code],'tableb'[Code])
Hope this helps.
Perfect, thanks Ashish
