Forum Discussion

bikeoholic's avatar
bikeoholic
Frequent Visitor
5 years ago
Solved

Count in SCD2 dimension - optimization

Hello PB community!   Can anybody please help me with DAX optimization in this scenario: I have Tabular model with 10 dimension tables, no real facts. One of the user request is to count units in ...
COUNT
Countdistinct
SCD2
  • AlB's avatar
    AlB
    5 years ago

    bikeoholic 

    Pending the response to my questions above, try this:

    Cnt of units V2 :=
CALCULATE (
    SUMX ( VALUES ( 'Units'[unit_id] ), 1 ),
    VAR minDate_ =MIN ( 'CalendarTable'[Date] )
    VAR maxDate_ = MAX ( 'CalendarTable'[Date] )
    RETURN
        FILTER (
            ALL ( 'Units'[unit_valid_from], 'Units'[unit_valid_to] ),
            NOT ( maxDate_ < 'Units'[unit_valid_from]
                || minDate_ > 'Units'[unit_valid_to] )
        )
)

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
5 years ago

bikeoholic 

Pending the response to my questions above, try this:

Cnt of units V2 :=
CALCULATE (
    SUMX ( VALUES ( 'Units'[unit_id] ), 1 ),
    VAR minDate_ =MIN ( 'CalendarTable'[Date] )
    VAR maxDate_ = MAX ( 'CalendarTable'[Date] )
    RETURN
        FILTER (
            ALL ( 'Units'[unit_valid_from], 'Units'[unit_valid_to] ),
            NOT ( maxDate_ < 'Units'[unit_valid_from]
                || minDate_ > 'Units'[unit_valid_to] )
        )
)

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
5 years ago

bikeoholic 

this could be better depending on the data distribution. It leverages the filters being applied to reduce the number of rows to scan 

Cnt of units :=
VAR minDate_ =
    MIN ( 'CalendarTable'[Date] )
VAR maxDate_ =
    MAX ( 'CalendarTable'[Date] )
RETURN
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                'Units',
                'Units'[unit_id],
                'Units'[unit_valid_from],
                'Units'[unit_valid_to]
            ),
            NOT ( maxDate_ < 'Units'[unit_valid_from]
                || minDate_ > 'Units'[unit_valid_to] )
        )
    )

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers