Forum Discussion
Helper IIHide empty fact rows while using SCD dimension measure that needs multiple filters
Hi Alexis,
I was afraid that changing the data model would be the best solution 😉 Your suggestion works for the order part, but the invoice table shows a stock value of 10 for all products, also those that should be showing 5. But as you said, it's not pretty and considering I have even more fact tables and this is not the only measure effected, this does not make much sense.
So there's no need to go into further analysis, I'll change the datamodel after all.
This still helped me a lot to dig deeper into DAX - thank you so much!
Best regards,
Kathrin
Helper IIHi Alexis,
thanks for your help again 🙂
That's not exactly what I need, I think my sample file was not build very well to show what I mean - I'll try it with following changes.
The orders 1,2,3 and 7 are all orders of product "A" (table "Initial Situaiton Order").
Product "A" had 10 pieces on stock until 2021-05-31 (highlihted red), which changed to 5 pieces as of 2021-06-01 (highlighted yellow).
The connection between fact and dimension is established via the ProductID (see arrows); as it is a slowly changing dimension, attributes should usually be shown with the as-of situation at a certain date in history. I have added an attribute "Supplier" for reference. Thus, OrderId 1,2 and 7 are shown with supplier "Sup1" that was valid until 2021-05-31 becuase the order date was dated before that; OrderId 3 is shown with supplier "Sup2" that was valid after or on 2021-06-01 (all marked in green rectangle).
In this special case of stock values I need to have a different approach, however. Here, I always need the current as-is situation, which is labeled with the "IsActive"-Flag (1 = current). Thus, I somehow need to keep the above logic but bypass it for this measure - In all four rows in the table "Order - wrong stock values, no empty lines" there should be stock value 5 (as shown with green arrow).
Here you can find the changed file: PBIX
Hope this explanation helps?
Super UserThis is hard since the ProductID you're relating tables with doesn't correspond to a single product but a product at a certain time. You might want to re-think your model to make this work more easily.
This is as close as I managed to get. Not pretty.
ProductStockValue_AO =
VAR ActiveProds =
CALCULATETABLE (
VALUES ( 'Product'[ProductID] ),
FILTER ( ALLEXCEPT ( 'Product', 'Product'[product] ), 'Product'[IsActive] = 1 )
)
VAR OrderProds = ALLSELECTED ( 'Order'[ProductID] )
VAR InvoiceProds = ALLSELECTED ( 'Invoice'[ProductID] )
RETURN
SWITCH (
TRUE (),
ISFILTERED ( 'Order' ),
IF (
NOT ISEMPTY ( 'Order' ),
CALCULATE (
SUM ( 'Product'[StockValue] ),
REMOVEFILTERS (),
'Product'[ProductID] IN INTERSECT ( ActiveProds, OrderProds )
)
),
ISFILTERED ( Invoice ),
IF (
NOT ISEMPTY ( 'Invoice' ),
CALCULATE (
SUM ( 'Product'[StockValue] ),
REMOVEFILTERS (),
'Product'[ProductID] IN INTERSECT ( ActiveProds, InvoiceProds )
)
),
SUM ( 'Product'[StockValue] )
)
- KaySunset
Hi Alexis,
I was afraid that changing the data model would be the best solution 😉 Your suggestion works for the order part, but the invoice table shows a stock value of 10 for all products, also those that should be showing 5. But as you said, it's not pretty and considering I have even more fact tables and this is not the only measure effected, this does not make much sense.
So there's no need to go into further analysis, I'll change the datamodel after all.
This still helped me a lot to dig deeper into DAX - thank you so much!
Best regards,
Kathrin