HOW?Calculated Column: if "this column" matches "this column" then display "this column" else "None"

[heathernicole]

I am trying to create a calculated column that looks at a couple of different columns - if they match, then display the number, if there's no match - display "No PO".

Here's an example: (there are actually thousands and thousands of rows for these two columns)

Type                          Number

Purchase Order          12345

Sales Order                 34567

Sales Order                12345

Sales Order                45769

Purchase Order          34567

Purchase Order          12567

What I need to get:

Type               Sales Order #     POs

Sales Order     34567                34567

Sales Order     12345                12345
Sales Order     45769                 "NO PO"

ANY assitance on this will be GREATLY appreciated!! 

The script I've created gets this error: DAX Comparison operations do not support comparing values of type Text.

Not very savvy with DAX yet...

Thanks in advance!!! 

[Sean · Accepted solution]

@ImkeF Thanks! This works great!

One thing I'll say if anyone is trying this don't rename a step "Filter Table" with a space

I don't know why it doesn't work with a space - use "FilteredTable" no space

Otherwise works great! Thanks again!

[Sean · Accepted solution]

You have to create a Relationship to the new Lookup Table you've just created...

[Sean]

@ImkeF Here's the result. I think this is what @heathernicole wanted to do?

[heathernicole]

@ImkeF @Sean 

Alright - I had to do a little bit of finagling but I think I've about got it. My only issue now is my calendar date slicers aren't working. That video was most helpful!

I'm going to check the numbers on the NO PO results. From what I can tell so far, it's right! 😄

Any ideas as to why the date slicers would stop working? 

[Sean · Accepted solution]

You have to create a Relationship to the new Lookup Table you've just created...

[heathernicole]

That's what I assumed as well - I'm reworking some things. I may need to add a few more columns.

[heathernicole · Accepted solution]

@ImkeF @Sean 

Here's what I had to do: 

the actual table I'm working with has a lot more columns and more values for that "Type" column. So what I did was 

1. Duplicated the core table - renamed it
2. Filtered for ONLY Sales Orders and Purchase Orders
3. Created a Reference table 
4. THEN used the video to finish the rest (merging queries).

In order to correct the date issue - I redid the steps and didn't take out any columns. Apparently one of the columns was a link to the Calendar table.

Once I did that - I manually created the relationship between the Referenced table (with all the calculations and filters) and the Calendar table. All slicers work and the data is correct!! 😄 

THANK YOU! THANK YOU! THANK YOU!

I've been working on this one for about 2 1/2 weeks so this was a huge win. Greatly appreciated the collaboration and desire to help! Thanks again!

[Ironpixel]

@heathernicole @Sean @ImkeF

This is a fantastic thread and helped me get a lions share of what I needed so thank you!

Could I add one more twist to this?

What if you have semicolon delineated values in one of your columns?

I managed to break these out to multiple columns, so when the value that I am comparing is in the first column this solution works great!  If it isn't this solution breaks down as it seems you can't compare multiple columns on source A to one column in Source B.

Any thoughts?

Example:

Source A

Source a was broken down from a single column having "Freddy; AMS; George" into multiple columns.

Tags.1	Tags.2	Tags.3
Acc
98gblaie	Acc
Bill	AMS
Freddy	AMS	George
Freddy	923450	BOW

Source B

Project

123

Acc

AMS

BOW

Any thoughts would be awesome!

Thanks!

[heathernicole]

@Ironpixel Hello! 🙂 I'm kind with @ImkeF - I don't quite understand the data yet either. I THINK I know where you're headed but you know what they say about assuming. 🙂 

Could you explain what you're after a little more? I get the column break down re: the one column into three. If you could give some more detail and explain your data structure a little more - I / we might be able to help you better. 🙂 

[Ironpixel]

Hi there,
Yes, maybe this will help,

I have two tables.

Source A has a column "Tags" that has data in it where one cell in that column might read "AMS; Freddy; George"
I have broken that data out to three colums where the structure and data would be.

Tags.1	Tags.2	Tags.3
AMS	Freddy	George

Source B has multiple columns but the column I care about would be titled "Project" with the subsequent data below.

Project

123

Acc

AMS

BOW

In the example in the main part of the thread, we learned how to merge data.  When following that example I was able to see when AMS in the two tables matched, in the new column it would print AMS when it matched, when it didn't i had it say, "Data Needs Alignment"

The problem is that merge data only works accros equal number of columns from both sources.

I am trying to figure out how to either search the origial semicolon dileneated cell for matching data, or search accross multiple columns.

Does that help?

Additional Sample Data below.

Original Source A

Tags
Acc
98gblaie; Acc
Bill; AMS
Freddy; AMS; George
Freddy; 923450; BOW

Modified Source A

Tags.1	Tags.2	Tags.3
Acc
98gblaie	Acc
Bill	AMS
Freddy	AMS	George
Freddy	923450	BOW

Source B

Project

123

Acc

AMS

BOW

[heathernicole]

@Ironpixel - what if you used Source B as a filter (slicer)? 

[Ironpixel]

@heathernicole

I am using a slicer on the new column generated from the aggregate of Source A and Source B.  If the columns match whats in Source B then you see Source B, if not I enter a text string that things aren't aligned creating a different value.  This way I know some items aren't matching.  Then in another visual, I list the items that don't match so that I can go in and fix the source.

[ImkeF]

Don't think I understand your data structure yet, but one possibility would be to add an index-column to SourceA, check that and unpivot others. That would bring everything into one column.

[Sean]

Great job!!!

[ImkeF]

@Sean Looking pretty good!

This should earn us a feedback at least 🙂

[heathernicole]

@ImkeF @Sean 

Hey! So sorry I missed the conversation on Friday! I was out of the office. Trying to catch up on a few things and then am going to dig into all of this. Thanks for working through this! Anxious to get my hands on this and see how it works

THANKS!! Will get back in touch after I've had a chance to read through all of this. 🙂 

[heathernicole]

That seems a lot simpler than what I generated... let me see if that will work. 🙂 

Thanks so much!!! 

[Sean]

If you do get an ERROR you will have to format the Number Column as TEXT.

[heathernicole]

Yeah it's still not working properly... 😞 

I'm getting Sales Orders that say NO PO and there's a matching Purchase Order right above it in the column. 

It says NO PO for the purchase Order, but the matching purchase order is right above it. I basically need to separate the columns if possible. 

Thanks so much for your help!! I'm getting closer to the goal! 🙂