DAX | To override active filter with Inactive (Userelationship)

Question

I need a help in creating DAX measures. Here is the relationship in Tabular cube. I have one Fact sales, Initial Date, final Date and product table.

Relationship : Product -> Fact Sales (1 to Many, active)
Initial Date -> Fact Sales (1 to many, inactive relation)
Final Date - > Fact Sales(1 to many, active)

Cube is used as source for Power Bi tools. Users can select any initial and final date for comparison of sales.

Actual data

Product|Date|Sales
Product1|20160101|100
Product1|20160102|110
Product1|20160131|200
Product2|20160101|78
.....

Expected output Filters :

product : Product1
Initial date : 20160101
Final Date : 20160131

Product | Initial Sales | Final Sales
Product1|100 |200

I have created DAX measure AS

Initial Sales := CALCULATE(SUM(SALES),USERELATIONSHIP('Fact Sales'[Date],'Initial Date'[Date]))

But this measure is not giving me initial sales as it is also filtered by final date.

Thanks in advance

alejandro zuleta alejandro zuleta · Accepted Answer · 2017-03-06T03:03:20

You can relate tables without an explicit relationship by using FILTER function in DAX.

Since Initial Date relationship with Fact Sales is disabled, you need to FILTER the Fact Sales rows that match the Initial Date selected in the filter. You can use EARLIER to compare the context being evaluated against each row in Fact Sales and filter the right values for the calculation.

Use these measures:

Initial Sales :=
IF (
    ISFILTERED ( InitialDate[InitialDate] ),
    CALCULATE (
        SUM ( FactSales[Sales] ),
        FILTER (
            ALL ( FactSales ),
            COUNTROWS (
                FILTER (
                    FactSales,
                    EARLIER ( FactSales[Date] ) = MAX ( InitialDate[InitialDate] )
                        && EARLIER ( FactSales[Product] ) = [Product]
                )
            )
        )
    ),
    BLANK ()
)

Final Sales :=
IF (
    ISFILTERED ( FinalDate[FinalDate] ),
    CALCULATE (
        SUM ( FactSales[Sales] ),
        FILTER ( FactSales, [Date] = MAX ( FinalDate[FinalDate] ) )
    ),
    BLANK ()
)

This expression works as follows, it sums all values in Sales column which Date is equal to the Final Date selected in the filter. However this can be calculated easily as you know with the relationship you have set between Final Date and Fact Sales.

The ISFILTERED functions tell us if the filter have a value selected or not, if a value is selected it returns the value otherwise returns BLANK.

This is an example in Power BI since I don't have access to Excel right now.

DAX | To override active filter with Inactive (Userelationship)

1 Answers