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 had an issue with a data set that I previously posted about. However, I've been given additional information/direction from the owners so now I'm dealing with a different challenges.
I have a list of items I'm try to measure against, all of which are collected throughout two columns assigned and completed. I left them as-is, and end up creating a series (about 10) columns to returns results with formulas based on the values or relationships between these columns. I have now split one of the cells by semi-colon into new rows. As a benefit this allows me a unique count of items. However, it breaks a formula I had that compares the two columns. For examples, I had:
Column A
Apple; Orange; Kiwi
Appple
Kiwi
Orange; Kiwi
Column B
Apple; Orange
Appple
{Blank}
Kiwi
Now, I have the following for just the first row:
Column A
Apple
Orange
Kiwi
Column B
Apple; Orange
Apple; Orange
Apple; Orange
How, I can I return the same if x, then true,fale results to compare the collective value of a series against the column B grouping? If it helps, each row contains a unique ID (which has been carried down to multiple rows when I split on column A).
Thanks.
Hi dmouser,
"How, I can I return the same if x, then true,fale results to compare the collective value of a series against the column B grouping?"
<--- Could you clarify more details about your logic to achieve this?
Regards,
Jimmy Tao
I may have solved this, but identified another challenge as a result. So I could leave my formulas unaltered, I added a step to copy the column, then split. Not sure if this is a best practice or not, but it allowed me to keep the original column and values to base my formula from.
However, my formula was comparing values of Column A to Column B to determine if they align. However, I noticed the sequences are not consistent, even though the values are. So if I'm comparing this Apple; Orange; Kiwi to Kiwi; Apple; Orange they don't match even though the content align. I do have the items in another table with their own IDs.
Can I sum of the values in the cell if they are found?
Fruite | ID
Apple = 1
Pear = 2
Orange = 3
Kiwi = 5
Peach = 4
Identified Fruits: Apple; Orange; Kiwi - sum of IDs = 9
Deliverd Fruits: Kiwi; Apple; Orange - sum of IDs = 9
Hi dmouser ,
"So if I'm comparing this Apple; Orange; Kiwi to Kiwi; Apple; Orange they don't match even though the content align. I do have the items in another table with their own IDs. "
<---Could you share some sample data and results you want to achieve and explain more details about your requirement?
Regards,
Jimmy Tao
I don't have a public file host, but did draft up a dummy workbook based on fruit. Image of two tables is below. I also uploaded a copy of the second table before manipulation to see how the sequence affects the outcome. Youll notice on the original while all 3 fruits were deliverred, because the sequence is different the calculation thinks they don't match and are therefore late.
I located the following post on the boards which - I think - gets to the same questions solution. However, the instructions are a bit choppy and I'm having difficulty following them. Ironically, the example uses fruit too.
Creating a total score for values in a concatenated and comma separated list
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |