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 have a problem that I don't manage to fix. I have to tables similar as the following ones:
TABLE1
Value | Start Interval | End Interval |
House | 1 | 5 |
Door | 6 | 10 |
Window | 11 | 15 |
TABLE 2
Asset | Number |
Sky | 7 |
Cloud | 90 |
What I am trying to achieve is TABLE2 to end up like this:
TABLE2 Updated
Asset | Number | Value |
Sky | 7 | Door |
Cloud | 90 |
Edit: I am trying to see if the Number from TABLE2 falls under any of the intervals defined in TABLE1 (including both ends)
I have tried using the following DAX query but I only receive blank values:
Value =
CALCULATE(FIRSTNONBLANKVALUE('TABLE1'[Value], TRUE()), FILTER('TABLE1', AND([Number] >= 'TABLE1'[Start Interval], [NUMBER] <= 'TABLE1'[End Interval]))
I really appreciate the help.
Thanks
Solved! Go to Solution.
HI @oliverL ,
Create a Calculated Column
Look21 =
VAR SearchValue =CALCULATE( MAX('Table2'[Number]))
RETURN
CALCULATE (
SELECTEDVALUE ( 'Table1'[Value], "aa" ),
FILTER (
ALLNOBLANKROW ( 'Table1'[Start Interval] , Table1[End Interval]),
'Table1'[Start Interval] <= SearchValue && Table1[End Interval] >= SearchValue
),
ALL ( Table1 )
)
//SearchValue
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hi @oliverL ,
Not sure how tables are related, you can try the following DAX.
Create a Calculated Column
Look = LOOKUPVALUE('Table 1'[Value],'Table 1'[Start Interval],Table2[Number])
You can also try Merge Queries in Power Query
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Hi @harshnathani ,
I am really looking to see if the number from Table2 falls into any of the intervals (including both ends). The lookupvalue function only works if the values are equal.
Thanks
HI @oliverL ,
Create a Calculated Column
Look21 =
VAR SearchValue =CALCULATE( MAX('Table2'[Number]))
RETURN
CALCULATE (
SELECTEDVALUE ( 'Table1'[Value], "aa" ),
FILTER (
ALLNOBLANKROW ( 'Table1'[Start Interval] , Table1[End Interval]),
'Table1'[Start Interval] <= SearchValue && Table1[End Interval] >= SearchValue
),
ALL ( Table1 )
)
//SearchValue
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Thanks! For some reason when I adapt this code to my real tables all I get is "aa". I will take a look to the transformations to see where the error may be coming, but the code clearly works. So I will mark it as solution.
Thanks again!
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |