Data-Model/Filter Propagation - Can a measure propagate 2 dynamic filters?

[rigelng1995]

Hi,

Consider a simple 3-table data model -  

Dimdate(Date)

DimCustomer - (CustomerID)

FactSales - (Date, CustomerID, SaleValue) 

Where 2 single directional, one to many relation is established from Dimdate and DimProducts to FactSales. 

With this simple model we can create measures such as - Total SaleValue = Sum(SaleValue)

Now I have created a 4th table (DimPromotion) which includes campaign data and I have organised it in the following structure - (Happy to re-structure it for a better solution) - Which includes the (campaignid, customerid, promotion_start_date, promotion_end_date.) 

Note - customers can be in multiple campaigns that are running in the same time. Each individual campaign can only have 1 start and end date. 

Example - 

campaign id - CustomerId -Promotion_start_date - Promotion_end_date

Campaign ID	CustomerID	Promotion_start_date	Promotion_end_date
1	A	25th June 2019	28th June 2019
1	B	25th June 2019	28th June 2019
2	A	27th June 2019	28th June 2019
2	C	27th June 2019	28th June 2019

How can I structure this model and create a measure called Total Campaign SaleValue so that I can look at the total sale value for each campaign?

In the above example - If I put campaign as a row and total campaign sale as a measure in the table - it would filter the FactSales table for member A and B and ALSO the dates between 25th 2019 and 28th 2019.  (However these 2 filters need to be dynamic based on the DimPromotion Table.)

End Result 

Table  -- Campaign ID, Total Campaign SaleValue) 

-- Campaign 1  -- (Total SaleValue for Member A,B between 25th June 2019 and 28th June 2019) 

-- Campaign 2  -- (Total SaleValue for Member A, C between 27th June 2019 and 28th June 2019)  

Is this doable in powerBI? 

Happy to write up a test powerBI file if the tables are too confusing.

Thanks

Rigel

[v-eachen-msft · Accepted solution]

Hi @rigelng1995 ,

You could create a measure like the following DAX:

Measure 2 =
SWITCH (
    SELECTEDVALUE ( DimPromotion[Campaign ID] ),
    1, CALCULATE (
        SUM ( FactSales[SaleValue] ),
        FILTER (
            FactSales,
            FactSales[Date] >= SELECTEDVALUE ( DimPromotion[Promotion_start_date] )
                && FactSales[Date] <= SELECTEDVALUE ( DimPromotion[Promotion_end_date] )
                && FactSales[CustomerID] IN VALUES ( DimPromotion[CustomerID] )
        )
    ),
    2, CALCULATE (
        SUM ( FactSales[SaleValue] ),
        FILTER (
            FactSales,
            FactSales[Date] >= SELECTEDVALUE ( DimPromotion[Promotion_start_date] )
                && FactSales[Date] <= SELECTEDVALUE ( DimPromotion[Promotion_end_date] )
                && FactSales[CustomerID] IN VALUES ( DimPromotion[CustomerID] )
        )
    )
)

Here is my test file. There are three methods in it.
Measure 2 will use your DimPromotion table.

Measure 1 will only use your "Campaign ID".

"New Table" is a table based on SUMMARIZE() and UNION() function.

You could refer to these methods.

[amitchandak]

@rigelng1995 , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Refer if this can help: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...