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 am new to DAX and I need help in writing dax query for comparing two columns from two tables and get a value based on the comparision.
Data for
Table 1 Table 2
Column1 Column_1 Value
A B 8
A C 13
A D 15
B E 20
B
C
D
D
D
E
E
F
G
G
H
Problem i am facing is i have to compare Column1 from Table1 with Column_1 in Table2. When there is a match record i have to display the corresponding value from Value column in Table2.
Any help is appreciated.
Solved! Go to Solution.
Hello @pjdsh ,
We can add an index column to your Table1 that will keep the visual from grouping the category (A, B, C, etc).
In the query editor, add a step to Add Column > Index
Then, in our Table1 we can pull over the value from Table2. The two tables have to be related on Column1.
Then in Table1 we add the calculated column to pull over the value and put 0 in the blanks.
Value = VAR TheValue = RELATED(Table2[Value]) RETURN IF ( ISBLANK ( TheValue ), 0, TheValue )
In our visual we add the index, column1 and value all from Table1 and you can make the index column skinny enough you don't see it, you just have to turn off wordwrap on headers and values.
I uploaded my sample file here for you to take a look at. https://www.dropbox.com/s/fvgv5uhdipjzas8/RelatedTable.pbix?dl=0
Is this the output you need?
Result i want to see is
A
A
A
B 8
B 8
C 13
D 15
D 15
D 15
E 20
E 20
F
G
G
H
I would like to see the result as following:
A 0
A 0
A 0
B 8
B 8
C 13
D 15
D 15
D 15
E 20
E 20
F 0
G 0
G 0
H 0
Hello @pjdsh ,
We can add an index column to your Table1 that will keep the visual from grouping the category (A, B, C, etc).
In the query editor, add a step to Add Column > Index
Then, in our Table1 we can pull over the value from Table2. The two tables have to be related on Column1.
Then in Table1 we add the calculated column to pull over the value and put 0 in the blanks.
Value = VAR TheValue = RELATED(Table2[Value]) RETURN IF ( ISBLANK ( TheValue ), 0, TheValue )
In our visual we add the index, column1 and value all from Table1 and you can make the index column skinny enough you don't see it, you just have to turn off wordwrap on headers and values.
I uploaded my sample file here for you to take a look at. https://www.dropbox.com/s/fvgv5uhdipjzas8/RelatedTable.pbix?dl=0
Thanks for your valueble inputs. I will try this approach.
The result i want to see is..
A 0
A 0
A 0
B 8
B 8
C 13
D 15
D 15
D 15
E 20
E 20
F 20
G 0
G 0
H 0
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |