Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
rigelng1995
Frequent Visitor

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

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 IDCustomerIDPromotion_start_datePromotion_end_date
1A25th June 201928th June 2019
1B25th June 201928th June 2019
2A27th June 201928th June 2019
2C27th June 201928th 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

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-eachen-msft
Community Support
Community Support

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

2 REPLIES 2
v-eachen-msft
Community Support
Community Support

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
amitchandak
Super User
Super User

@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...




Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.