Count in SCD2 dimension - optimization

[bikeoholic]

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 Units table through time.  This table is a SCD2 table type and only 2 dates in table are valid_from and valid_to. They will use year-month and year attributes on reports and in slicers. Beside date attribute they wan't to use attributes Units Type and Country of Origin.

I did't connect my Calendar table with Units table in Tabular model and I have combined this DAX code:

Cnt of units:=
CALCULATE (
   // DISTINCTCOUNT ( 'Units'[unit_id] ),
   SUMX(VALUES('Units'[unit_id] ),1),
    FILTER (
        'Units',
        COUNTROWS (
            FILTER (
                VALUES ( 'CalendarTable'[Date] ),         
                'CalendarTable'[Date]>= 'Units'[unit_valid_from]
                    && 'CalendarTable'[Date] <= 'Units'[unit_valid_to]
            )
        )
    )
)

So this code is doing some sort of cross join / cross aplly on date table and since Units table isn't really small (it has 500K+ rows) it's slow. Calendar table is from DB Model, not from PB and has data from 2015 to 2025.

Current server timing from dax studio are like this:

In grid: Year Month, Unit type, Country of origin and my metric

Filter: Year in (2020, 2019)

I already replaced DISTINCOUNT with SUMX code. With using DISTINCOUNT function:

So, are there are ideas how to optimize this code even more? Is there any way I can solve this differently (connections in Tabular with Calendar table, etc..?)

Similar problems: https://community.powerbi.com/t5/Desktop/How-to-find-number-of-customers-in-a-SCD2-type-table/m-p/97... 

[AlB]

@bikeoholic 

4 secs is still considerable and it's engaging the FE significantly.  I'm curious if this version will be faster:

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

 or this:

Cnt of units V2_C :=
VAR minDate_ =
    MIN ( 'CalendarTable'[Date] )
VAR maxDate_ =
    MAX ( 'CalendarTable'[Date] )
RETURN
    COUNTROWS (
        FILTER (
            ALL ( 'Units'[unit_id], 'Units'[unit_valid_from], 'Units'[unit_valid_to] ),
             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]

@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]

@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 

[bikeoholic]

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!

[AlB · Accepted solution]

@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]

@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 

[AlB]

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