Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have two trimmed down fact tables and two dimension tables. The dimesnion tables filter both fact tables.
For my inventory (fact) table I want to retrieve the order id from the orders table. In the report there is a table showing inventory details at the level of [Key_Product_businessUnit].
My Measure is
OrderID =
VAR VAR_Key_Product_businessUnit = SELECTEDVALUE('inventory'[Key_Product_businessUnit])
VAR VAR_Result =
CALCULATE(
MAX('Orders'[OrderID]),
'Orders'[Key_Product_businessUnit] = VAR_Key_Product_businessUnit
)
RETURN
VAR_Result
Is this the best pattern to use. It seems to be really slow? There is only one row (or so the business users tell me) in Orders for each row in inventory.
JUst does not seem efficient.
Thanks
Solved! Go to Solution.
Hi @ells69
You may try with TREATAS. It is not usually super fast but it could improve the performance in some scenarios.
OrderID =
IF (
HASONEVALUE ( 'inventory'[Key_Product_businessUnit] ),
CALCULATE (
MAX ( 'Orders'[OrderID] ),
TREATAS (
VALUES ( 'inventory'[Key_Product_businessUnit] ),
'Orders'[Key_Product_businessUnit]
)
)
)
Hi @ells69
You may try with TREATAS. It is not usually super fast but it could improve the performance in some scenarios.
OrderID =
IF (
HASONEVALUE ( 'inventory'[Key_Product_businessUnit] ),
CALCULATE (
MAX ( 'Orders'[OrderID] ),
TREATAS (
VALUES ( 'inventory'[Key_Product_businessUnit] ),
'Orders'[Key_Product_businessUnit]
)
)
)
If there is only 1 order then you could use LOOKUPVALUE, e.g.
OrderID Measure =
VAR VAR_Key_Product_businessUnit =
SELECTEDVALUE ( 'inventory'[Key_Product_businessUnit] )
VAR VAR_Result =
LOOKUPVALUE (
'Orders'[OrderID],
'Orders'[Key_Product_businessUnit], VAR_Key_Product_businessUnit
)
RETURN
VAR_Result
You could also do it as a calculated column like
OrderID Column =
LOOKUPVALUE (
'Orders'[OrderID],
'Orders'[Key_Product_businessUnit], 'inventory'[Key_Product_businessUnit]
)
You would probably get better performance doing it as a column.
User | Count |
---|---|
53 | |
46 | |
19 | |
16 | |
13 |
User | Count |
---|---|
108 | |
56 | |
29 | |
21 | |
18 |