Compare two columns' values

[BIBOB]

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:

Allocation =

IF(

SUM('Task List'[Allocation Requested]) > SUM('Task List'[Allocation Actual]),

"Requested too high"

)

I'm pretty sure the sum part is one of the main reason it's not working as intended but I can't make any other meaningful changes to the measure without it giving complete bonkers result.

Any input is appreciated, I'd like to start a discussion 🙂

[gordykenmuir]

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.

[Ashish_Mathur]

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.

[gordykenmuir]

Perfect, thanks Ashish

[Ashish_Mathur]

You are welcome.

[parry2k]

@BIBOB my apologies, there should be a comma after TRUE(), sorry about that.

[parry2k]

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

[parry2k]

@BIBOB just sent the solution, add as a column. 

[parry2k · Accepted solution]

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

[BIBOB]

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"

)

[BIBOB]

I've tried your formula, but to no avail..

I get 

[parry2k]

@BIBOB allocation request and allocation actual, are these columns or measure?

[BIBOB]

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

[BIBOB]

Has anyone else had any similar experiences ?