topic Re: Count in SCD2 dimension - optimization in DAX Commands and Tips

Count in SCD2 dimension - optimization

bikeoholic — Tue, 10 Nov 2020 11:09:57 GMT

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..?)

Re: Count in SCD2 dimension - optimization

AlB — Tue, 10 Nov 2020 12:17:35 GMT

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

Re: Count in SCD2 dimension - optimization

AlB — Tue, 10 Nov 2020 12:35:44 GMT

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

Re: Count in SCD2 dimension - optimization

AlB — Tue, 10 Nov 2020 12:38:51 GMT

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

Re: Count in SCD2 dimension - optimization

bikeoholic — Tue, 10 Nov 2020 15:43:18 GMT

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!

Re: Count in SCD2 dimension - optimization

bikeoholic — Wed, 11 Nov 2020 11:18:48 GMT

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

Re: Count in SCD2 dimension - optimization

AlB — Wed, 11 Nov 2020 11:30:19 GMT

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

Re: Count in SCD2 dimension - optimization

AlB — Wed, 11 Nov 2020 11:49:54 GMT

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