Forum Discussion

Greenwoodr's avatar
Greenwoodr
Helper I
7 years ago

Understanding Multiple Fact Tables

Hi I would be grateful for some assitance with the following data model design query.......     I have the following structure    1) Fact Table 1 Sales :(1 row per sale) : Contains details of s...
Need Help
PaulDBrown's avatar
PaulDBrown
Community Champion
4 years ago

See if this works, though the performance is slow:

Sales By Campaign & Customer =
VAR StartDate =
    CALCULATE (
        MAX ( 'Campaign Table'[Start Date] ),
        ALLEXCEPT ( 'Campaign Table', 'Campaign Table'[CampaignID] )
    )
VAR StartKey =
    INT ( DAY ( StartDate ) & MONTH ( StartDate ) & YEAR ( StartDate ) )
VAR EndDate =
    CALCULATE (
        MAX ( 'Campaign Table'[End Date] ),
        ALLEXCEPT ( 'Campaign Table', 'Campaign Table'[CampaignID] )
    )
VAR EndKey =
    INT ( DAY ( EndDate ) & MONTH ( EndDate ) & YEAR ( EndDate ) )
RETURN
    CALCULATE (
        [Sum Sales],
        CROSSFILTER ( 'Campaign Table'[Customer Code], 'Customer Table'[Customer Code], BOTH ),
        FILTER (
            ALL ( 'Sales Table'[DateKey] ),
            'Sales Table'[DateKey] >= StartKey
                && 'Sales Table'[DateKey] <= EndKey
        )
    )

 

I've attached the sample PBIX file