Get description from unrelated table with Table 1 ID between Table 2 From ID and To ID in Power BI direct query mode

Question

I am using Power BI with Direct Query mode pulling data in from Snowflake. The request has the restriction of not being allowed to join or merge tables.

Table 1 is a list of orders with a Priority value.

order_id	priority
AB001	17
AB002	35
AB003	70
AB004	24

Table 2 is a list of Priorities with a priority from value and to value.

fr_priority	to_priority	descr
1	20	PM
21	40	Routine
41	60	Urgent
61	80	Emergency

The end goal for the visualization is to have a column showing the order ID from Table 1 and the priority description from Table 2.

order_id	priority_description
AB001	PM
AB002	Routine
AB003	Emergency
AB004	Routine

Adding a new column from model view to Table 1 does not allow referencing Table 2 columns or DAX functions. In T-SQL, I would normally join the tables:

select T1.order_id, T2.descr
from Table_1 T1 with(nolock)
left outer join Table_2 T2 with(nolock) on T2.fr_priority <= T1.priority and T2.to_priority >= T1.priority 

How can this be achieved in Power BI direct query mode without a join query being sent to Snowflake?

Answer 1

you can try to create a column

Column =
MAXX (
    FILTER (
        'Table 2',
        'Table 1'[priority] >= 'Table 2'[fr_priority]
            && 'Table 1'[priority] <= 'Table 2'[to_priority]
    ),
    'Table 2'[descr]
)

---

if you want to create a measure,you can try this

Measure =
MAXX (
    FILTER (
        'Table (2)',
        MAX ( 'Table'[priority] ) >= 'Table (2)'[fr_priority]
            && MAX ( 'Table'[priority] ) <= 'Table (2)'[to_priority]
    ),
    'Table (2)'[descr]
)