Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
What I am trying to accomplish with no success is the following.
Table 1 (Simplified for example)
Unit Num
A1111 |
B2222 |
C3333 |
Table 2
Date Unit NumColor
1/1/2021 | A1111 | Blue |
1/2/2021 | B2222 | Blue |
1/3/2021 | C3333 | Blue |
1/4/2021 | A1111 | Red |
1/5/2021 | B2222 | Yellow |
1/6/2021 | C3333 | Blue |
1/7/2021 | B2222 | Blue |
1/8/2021 | C3333 | Red |
Table 1 Result
Unit Num Color Value
A1111 | 5 |
B2222 | 0 |
C3333 | 5 |
I am attempting to create a new column (Color Value on Table 1 Result) on Table 1 that Matches the Unit Num on Table 1 with the Unit on Table 2. It also needs to look up the MAX Date and if Num Color is Red return 5 if True and 0 if False.
I really appreciate you taking out time in your very busy lives to look into this for me. Thank You!
Solved! Go to Solution.
@MichaelBristol
I created a column that returns the result you are looking for:
Color Value =
var __maxdate =
MAXX(
FILTER(
'Table 2',
'Table 2'[Unit] = 'Table 1'[Unit Num]
),
'Table 2'[Date]
)
var __result =
IF(
ISEMPTY(
FILTER(
'Table 2',
'Table 2'[Unit] = 'Table 1'[Unit Num] &&
'Table 2'[Date] = __maxdate &&
'Table 2'[NumColor] = "Red"
)
),
0,
5
)
return
__result
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@MichaelBristol
I created a column that returns the result you are looking for:
Color Value =
var __maxdate =
MAXX(
FILTER(
'Table 2',
'Table 2'[Unit] = 'Table 1'[Unit Num]
),
'Table 2'[Date]
)
var __result =
IF(
ISEMPTY(
FILTER(
'Table 2',
'Table 2'[Unit] = 'Table 1'[Unit Num] &&
'Table 2'[Date] = __maxdate &&
'Table 2'[NumColor] = "Red"
)
),
0,
5
)
return
__result
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Your solution worked famously! Thank you.
Hi, @MichaelBristol
Please check the below picture and the sample pbix file's link down below.
It is for creating a new column in Table1.
Color Value =
VAR currentunit = Table1[Unit Num]
VAR maxdatetable1 =
CALCULATE (
MAX ( Table2[Date] ),
FILTER ( Table2, Table2[Unit] = currentunit )
)
RETURN
IF (
CALCULATE (
SELECTEDVALUE ( Table2[NumColor] ),
FILTER ( Table2, Table2[Unit] = currentunit && Table2[Date] = maxdatetable1 )
) = "Red",
5,
0
)
https://www.dropbox.com/s/l0tpcho7epxz37b/michaelbristol.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
While your solution looks like it should have worked, it created a Circular Dependency in my table. Thank you so much for looking into this!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
110 | |
109 | |
94 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
103 | |
86 |