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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!