Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!!
Solved! Go to 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!
You have to create a Relationship to the new Lookup Table you've just created...
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
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!
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?
You have to create a Relationship to the new Lookup Table you've just created...
That's what I assumed as well - I'm reworking some things. I may need to add a few more columns.
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
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!
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!
@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. 🙂
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 |
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
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
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. 🙂
That seems a lot simpler than what I generated... let me see if that will work. 🙂
Thanks so much!!!
If you do get an ERROR you will have to format the Number Column as TEXT.
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! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |