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 

bikeoholic's avatar
bikeoholic
Frequent Visitor
5 years ago

AlBThank you, your first code works like a charm 😎

Result after your DAX code:

 

In second code I don't get the right results. I didn't had time yet to go in deep why..

 

Best regards, v

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

    bikeoholic 

    The second should do the same as the first. If the results are not right, it probably has to do with the relationships on the model. I made some assumptions, like no relationship between the date table and the Units table, that seemingly do not hold.

     

    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