cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KaySunset
Helper I
Helper I

CROSSFILTER to use one dimension measure with multiple fact analyses

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!

 

ProductStockValue_Test1 = IF(SELECTEDVALUE ( 'Order'[ProductID] ) = SELECTEDVALUE ( 'Product'[ProductID] )
,CALCULATE(SUM('Product'[StockValue]) ,CROSSFILTER('Product'[ProductID],'Order'[ProductID],BOTH))
,IF ( SELECTEDVALUE ( 'Invoice'[ProductID] ) = SELECTEDVALUE ( 'Product'[ProductID] )
,CALCULATE(SUM('Product'[StockValue]) ,CROSSFILTER('Product'[ProductID],'Invoice'[ProductID],BOTH))
,CALCULATE(SUM('Product'[StockValue]) )) )
 
ProductStockValue_Test2 = IF(ISFILTERED('Order'[ProductID])=TRUE()
              ,CALCULATE(SUM('Product'[StockValue]),CROSSFILTER('Product'[ProductID],'Order'[ProductID],BOTH))
              ,IF(ISFILTERED('Invoice'[ProductID])=TRUE()
                    ,CALCULATE(SUM('Product'[StockValue]),CROSSFILTER('Product'[ProductID],'Invoice'[ProductID],BOTH))))
 
 
 

Best regards,

Kathrin

 

 

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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.

View solution in original post

2 REPLIES 2
KaySunset
Helper I
Helper I

Hi Alexis,

 

works like a charm, thank you so much!

 

BR,

Kathrin

AlexisOlson
Super User
Super User

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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors