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.
Hello!
I have a requirement to replace any blank values in a column with a different value. While this can be done with an IF statement in the same table, the replacement value is in a different table. I've tried using RELATED but it isn't working, getting the error "A single value or column 'value' in table 'table' cannot be determined" even though Intellisense recognizes the table.
Doing some research I believe that RELATED only works when the tables are directly related. However, in this situation the data is split between four individually linked tables (below). Unfortunately I cannot change how these relationships are laid out.
Placing table 1 and table 4 values into the same visual table works perfectly, so the tables are setup correctly. Here is a visual of what I want to happen:
Table 1[value 1] | Table 4[value4] |
Eggs | Store 1 |
(blank value, so it should display Store 2) | Store 2 |
Milk | Store 3 |
Is there any way to make this requirement happen? Thanks!
DAX IF Statement:
Solved! Go to Solution.
Figured it out! Added this to a new column based on the test data:
Figured it out! Added this to a new column based on the test data:
Hi, @kjkjkjnk ;
Since the data is sensitive, you can create some fake data, and the results you want to output.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-yalanwu-msft ,
I created a sample report with sample data. I couldn't attach the report directly, but found an upload service suggested on this forum.
Below is a screenshot from the report. The end goal is to replace that blank value in the 'Table 1"names"' column with "AccountValue2" from the 'Table 4'account' column. All of the relationships are the same as the real data. However, there are several other tables in the live data connected to these tables, which is why I can't modify the relationships (the data is imported from Dynamics 365).
Upload files for free - Table Value Replacement Fake Data.zip - ufile.io
Hi, @kjkjkjnk ;
RELATED is generally used in one-to-one or one-to-many relationships, so your example is not suitable for using the RELED function, maybe you can use the LOOKUPVALUE() function,
Since I can't restore your model relationships, I don't know much about the specific connection relationship fields of your 4 tables; So can't give a precise solution, can you share a simple small example of four tables? And what are the columns of the four table join relationships?
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @v-yalanwu-msft !
Thank you for the help! To answer your question, here are how the tables are linked together. Because the data is sensitive I can't give specific examples, so if you need more info than this just let me know and I'll try to clarify.
The data I'm trying to share are both text based. Table 1 contains names, table 4 contains locations.
Table 1 is related to table 2 through a unique name ID (IE: 12345 for John). These show up multiple times in each table.
Talbe 2 is related to table 3 through a unique location ID. This shows up multiple times in table 2
Table 3 is related to table 4 through a unique account ID. This shows up multiple times in table 3.
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 |
---|---|
114 | |
100 | |
78 | |
75 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |