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.
Hi,
I'm trying to create a relationship between elements in different tables with the distinct union DAX, but it's returning a null/blank result in the new table, which then won't let me create relationships.
I've used this before and it's worked fine..
Can anyone help with this?
Thank you!
Solved! Go to Solution.
First, use DISTINCT instead of VALUES, so to avoid the blank row which might be created due to invalid relationships. Then, if BLANK is present somwhere among teh values, you can simply surround the whole code with a FILTER that removes the blank:
FILTER ( DISTINCT ( UNION ( DISTINCT ( T1[C] ), DISTINCT ( T2[C] ) ) ), NOT ISBLANK ( [C] ) )
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
First, use DISTINCT instead of VALUES, so to avoid the blank row which might be created due to invalid relationships. Then, if BLANK is present somwhere among teh values, you can simply surround the whole code with a FILTER that removes the blank:
FILTER ( DISTINCT ( UNION ( DISTINCT ( T1[C] ), DISTINCT ( T2[C] ) ) ), NOT ISBLANK ( [C] ) )
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
When using NOT ISBLANK for only one column wrapped inside the DISTINCT() it's clear. But when aggregating 2 columns wrapped in a UNION() I didn't understand why the engine just gives me the first table[column] as a option to wrap inside the filter function.
For example, when I use the NOT ISBLANK("I only can add the T1[C] option)
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 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |