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.
This should be simple as it is a wash in SQL. However I am having no luck. TABLE A has measured DEPTHS per RAIL. TABLE B assigns COLOR between TOP and BOTTOM depths. Because this is a many-to many relationship, I created a bridge table (TABLE C) with one to many joins to both the A and B tables. The end result I am trying to achieve is that if the Table A RAIL = TABLE B RAIL and the TABLE A DEPTH falls between the TABLE B TOP and BOTTOM depths then post the TABLE B COLOR. I either get blanks or True/False. Please provide me the correct DAX formula to accomplish this.
Solved! Go to Solution.
Hi, @cookm
You can try the following methods.
Color =
CALCULATE ( MAX ( 'Table B'[COLOR] ),
FILTER ( ALL ( 'Table B' ),
[RAIL] = EARLIER ( 'Table A'[RAIL] )
&& [TOP] <= EARLIER ( 'Table A'[DEPTH] )
&& [BOTTOM] >= EARLIER ( 'Table A'[DEPTH] )
)
)
Measure =
CALCULATE ( MAX ( 'Table B'[COLOR] ),
FILTER ( ALL ( 'Table B' ),
[RAIL] = SELECTEDVALUE( 'Table A'[RAIL] )
&& [TOP] <= SELECTEDVALUE ( 'Table A'[DEPTH] )
&& [BOTTOM] >= SELECTEDVALUE( 'Table A'[DEPTH] )
)
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @cookm
You can try the following methods.
Color =
CALCULATE ( MAX ( 'Table B'[COLOR] ),
FILTER ( ALL ( 'Table B' ),
[RAIL] = EARLIER ( 'Table A'[RAIL] )
&& [TOP] <= EARLIER ( 'Table A'[DEPTH] )
&& [BOTTOM] >= EARLIER ( 'Table A'[DEPTH] )
)
)
Measure =
CALCULATE ( MAX ( 'Table B'[COLOR] ),
FILTER ( ALL ( 'Table B' ),
[RAIL] = SELECTEDVALUE( 'Table A'[RAIL] )
&& [TOP] <= SELECTEDVALUE ( 'Table A'[DEPTH] )
&& [BOTTOM] >= SELECTEDVALUE( 'Table A'[DEPTH] )
)
)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi @cookm
try to create a calculated column in TableA like:
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
35 | |
32 | |
18 | |
18 |