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

Hi bikeoholic 

I suppose you cannot share the pbix?

Is Cnt of units  a measure? Are you using it with any filters applied? What are the active relationships?

Please explain a bit more what you mean by One of the user request is to count units in Units table through time

Do ee want the units that have both 'Units'[unit_valid_from] and 'Units'[unit_valid_to] within the date period selected??

Best if you can provide an example

 

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