cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heathernicole
Continued Contributor
Continued Contributor

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

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

~heathernicoale
3 ACCEPTED SOLUTIONS
Sean
Community Champion
Community Champion

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

 

View solution in original post

Sean
Community Champion
Community Champion

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

 

View solution in original post

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

~heathernicoale

View solution in original post

37 REPLIES 37
Sean
Community Champion
Community Champion

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

 

Result.png

heathernicole
Continued Contributor
Continued Contributor

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

~heathernicoale
Sean
Community Champion
Community Champion

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

 

View solution in original post

heathernicole
Continued Contributor
Continued Contributor

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

 

~heathernicoale

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

~heathernicoale

View solution in original post

@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.1Tags.2Tags.3
Acc  
98gblaieAcc 
BillAMS 
FreddyAMSGeorge
Freddy923450BOW

 

Source B

Project
123
Acc
AMS
BOW

 

Any thoughts would be awesome!

Thanks!

heathernicole
Continued Contributor
Continued Contributor

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

~heathernicoale

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.1Tags.2Tags.3
AMSFreddyGeorge

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.1Tags.2Tags.3
Acc  
98gblaieAcc 
BillAMS 
FreddyAMSGeorge
Freddy923450BOW

 

Source B

Project
123
Acc
AMS
BOW

 

heathernicole
Continued Contributor
Continued Contributor

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

~heathernicoale

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

 

 

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Sean
Community Champion
Community Champion

Great job!!!

@Sean Looking pretty good!

This should earn us a feedback at least 🙂

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

heathernicole
Continued Contributor
Continued Contributor

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

~heathernicoale
heathernicole
Continued Contributor
Continued Contributor

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

 

Thanks so much!!! 

~heathernicoale
Sean
Community Champion
Community Champion

Check CC.png

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

heathernicole
Continued Contributor
Continued Contributor

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. 

 

Comparison.JPG

 

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! 🙂 

~heathernicoale

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!