powerbidaxssas

DAX script for measure based on date dimention


In SSAS I have the fact table:

ContractID DateFrom DateTo
1001 2024-06-01 2024-06-20
1002 2024-06-05 2024-06-10
1002 2024-06-15 2024-07-10
1003 2024-06-20 2024-08-05

and Date dimention:

Year MonthName MonthId Date
2024 June 6 2024-06-01
2024 June 6 2024-06-02

...

There is no relationship between the fact table and Date dimention.

I need to create a measure with a unique count of ContractID, where Date value (Date dimention) between DateFrom and DateTo (fact table). The measure should work when choosing any date level from Date dimention: year, year-month, year-month-date.

CountOfContracts :=
CALCULATE(
           DISTINCTCOUNT('FactTable'[ContractId]),
           FILTER('FactTable',
                     (
                        SELECTEDVALUE('DateDimention'[Date]) >= 'FactTable'[DateFrom] &&
                        SELECTEDVALUE('DateDimention'[Date]) <= 'FactTable'[DateTo]
                     )
                  )
         )

Problem: this doesn't work with date at month and year level


Solution

  • Pieter, thanks for the advice, I was thinking of doing the same, but there is a risk of greatly increasing the size of the fact table. This is how I managed to solve the problem:

    CountOfContracts :=
    var MonthId = LOOKUPVALUE('DateDimention'[year_month_order/* format YYYYMM */],'DateDimention'[Year], SELECTEDVALUE('DateDimention'[Year]), 'DateDimention'[MonthId], SELECTEDVALUE('DateDimention'[MonthId]))
    return
    switch
    (
        true,
        HASONEVALUE('DateDimention'[Year]) && not HASONEVALUE('DateDimention'[MonthId]),
                     CALCULATE(
                                DISTINCTCOUNT('FactTable'[ContractID]),
                    FILTER('FactTable',
                    (
                        SELECTEDVALUE('DateDimention'[Year]) >= year('FactTable'[DateFrom]) &&
                        SELECTEDVALUE('DateDimention'[Year]) <= year('FactTable'[DateTo])
                    )
                          )
                ), 
    
                    HASONEVALUE('DateDimention'[MonthId]) && not HASONEVALUE('DateDimention'[Date]),
            CALCULATE(
                   DISTINCTCOUNT('FactTable'[ContractID]),
                    FILTER('FactTable',
                    (
                        MonthId >= year('FactTable'[StartDate])*100 + month('FactTable'[DateFrom]) &&
                        MonthId <= year('FactTable'[EndDate])*100 + month('FactTable'[DateTo])
                    )
                    )
                ),
    
            HASONEVALUE('DateDimention'[Date]),
            CALCULATE(
                  DISTINCTCOUNT('FactTable'[ContractID]),
                    FILTER('FactTable',
                    (
                        SELECTEDVALUE('DateDimention'[Date]) >= 'FactTable'[DateFrom] &&
                        SELECTEDVALUE('DateDimention'[Date]) <= 'FactTable'[DateTo]
                    )
                    )
                ),
    
            BLANK()
    )