Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all,
I have a rather complex problem, at least I think it is. I do have a sample file further down, if you'd like to play around.
Assume a star schema model, in a very simple form with two fact tables and one product dimension. Within the product dimension, there's a stock value column. I created a measure [ProductStockValue] to be able to show the product stock value in various combinations, e.g. either in combination with Orders or with Invoices - always depending on the current question to analyze.
My approach was to use the CROSSFILTER Funktion, which would be easy if it was just one fact table. Is there a way, to somehow query what fact table I am using and use a corresponding version of the CROSSFILTER function?
I have tried the following code snippets for my measure, for clear understanding please see the following sample file: PBIX FILE
Looking forward to your help!
Best regards,
Kathrin
Solved! Go to Solution.
How about this?
ProductStockValue_AO =
SWITCH (
TRUE (),
ISFILTERED ( 'Order' ), CALCULATE ( SUM ( 'Product'[StockValue] ), 'Order' ),
ISFILTERED ( Invoice ), CALCULATE ( SUM ( 'Product'[StockValue] ), Invoice ),
SUM ( 'Product'[StockValue] )
)
Note that if you update your second formula slightly, it sort of works:
ProductStockValue_Test2 =
IF (
ISFILTERED ( 'Order'[OrderID] ) = TRUE (),
CALCULATE (
SUM ( 'Product'[StockValue] ),
CROSSFILTER ( 'Product'[ProductID], 'Order'[ProductID], BOTH )
),
IF (
ISFILTERED ( 'Invoice'[InvoiceID] ) = TRUE (),
CALCULATE (
SUM ( 'Product'[StockValue] ),
CROSSFILTER ( 'Product'[ProductID], 'Invoice'[ProductID], BOTH )
)
)
)
It works since you have OrderID or InvoiceID in the table but wouldn't if you used a name instead of an ID. ISCROSSFILTERED checks for indirect filters but that would be too broad since Product filters both other tables and you has a filter on it.
Hi Alexis,
works like a charm, thank you so much!
BR,
Kathrin
How about this?
ProductStockValue_AO =
SWITCH (
TRUE (),
ISFILTERED ( 'Order' ), CALCULATE ( SUM ( 'Product'[StockValue] ), 'Order' ),
ISFILTERED ( Invoice ), CALCULATE ( SUM ( 'Product'[StockValue] ), Invoice ),
SUM ( 'Product'[StockValue] )
)
Note that if you update your second formula slightly, it sort of works:
ProductStockValue_Test2 =
IF (
ISFILTERED ( 'Order'[OrderID] ) = TRUE (),
CALCULATE (
SUM ( 'Product'[StockValue] ),
CROSSFILTER ( 'Product'[ProductID], 'Order'[ProductID], BOTH )
),
IF (
ISFILTERED ( 'Invoice'[InvoiceID] ) = TRUE (),
CALCULATE (
SUM ( 'Product'[StockValue] ),
CROSSFILTER ( 'Product'[ProductID], 'Invoice'[ProductID], BOTH )
)
)
)
It works since you have OrderID or InvoiceID in the table but wouldn't if you used a name instead of an ID. ISCROSSFILTERED checks for indirect filters but that would be too broad since Product filters both other tables and you has a filter on it.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |