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

Hi !

 

No, sorry, I cannot share PB file 😞

Yes, metric ofcourse, named "Cnt Count of Units". I didn't wrote down as well that this DAX code gets the job done..it works, but it's slow. I mean 25s is not much, but table will get bigger and bigger.

There are only active relationships in Tabular model. To me it's a straight forward Tabular model. Nothing "fancy". This metric is defined in Tabular and not in PB.

 

Below is an example of few random rows and columns from "Units" table. For each unit there is a valid from and to date. Unit can be valid throuh many months or just 1 month. They can be valid 1 day, but I don't care for day/date level. I have to count 1st row only in Jan 2020, 2nd row in all next months..and so on.

 

This is an example of a table visualisation on a report:

 

 

Tnx, I will try your suggestions!