DAX Optimization - Find out number of accounts with two commodities but in different commodity group

[reuben521]

Hi,

I have a table that contains customer contracts, and it looks like the following:

CONTRACT_START_DATE	CONTRACT_END_DATE	Commodity	Commodity Group	Account Number	Site ID
3-Jan-09	6-Jan-09	Commodity 1	Group A	1131	95821
25-Dec-08	25-Dec-08	Commodity 2	Group B	1237	40414
2-Jan-09	2-Jan-09	Commodity 1	Group A	1237	69001
2-Jan-09	2-Jan-09	Commodity 1	Group A	1237	74005
31-Dec-08	1-Jan-09	Commodity 1	Group A	2017	1565
3-Jan-09	6-Jan-09	Commodity 1	Group A	2068	60490
30-Dec-08	1-Jan-09	Commodity 1	Group A	4579	40338
30-Dec-08	6-Jan-09	Commodity 1	Group A	5086	78650
31-Dec-08	6-Jan-09	Commodity 1	Group A	6971	77416
18-Dec-08	6-Jan-09	Commodity 1	Group B	10203	29024
31-Dec-08	3-Jan-09	Commodity 1	Group B	10277	27474
3-Jan-09	3-Jan-09	Commodity 2	Group B	11116	68412
24-Dec-08	31-Dec-08	Commodity 1	Group B	14257	35815
25-Dec-08	3-Jan-09	Commodity 1	Group A	15274	75172
24-Dec-08	3-Jan-09	Commodity 1	Group A	15284	78766
3-Jan-09	6-Jan-09	Commodity 1	Group A	15412	82916
31-Dec-08	1-Jan-09	Commodity 1	Group B	15637	79614
1-Jan-09	7-Jan-09	Commodity 1	Group B	17520	91613
11-Dec-08	5-Jan-09	Commodity 1	Group A	19260	1617
4-Jul-08	4-Jul-08	Commodity 1	Group A	20589	92557
6-Dec-08	1-Jan-09	Commodity 1	Group B	21806	99768
25-Dec-08	5-Jan-09	Commodity 1	Group A	21997	45567
20-Dec-08	6-Jan-09	Commodity 1	Group B	22596	866
4-Jan-09	4-Jan-09	Commodity 1	Group A	23654	24111
5-Dec-08	4-Jan-09	Commodity 2	Group B	23796	85511
31-Dec-08	3-Jan-09	Commodity 1	Group B	24426	93451
17-Dec-08	5-Jan-09	Commodity 1	Group A	24696	13947
31-Dec-08	3-Jan-09	Commodity 1	Group A	25039	56467
13-Dec-08	13-Dec-08	Commodity 1	Group B	25429	27841
1-Jan-09	7-Jan-09	Commodity 1	Group B	27991	19861
21-Dec-08	3-Jan-09	Commodity 1	Group B	29146	92076
2-Jan-09	2-Jan-09	Commodity 1	Group A	29255	40446
18-Dec-08	6-Jan-09	Commodity 1	Group A	30873	725
11-Dec-08	11-Dec-08	Commodity 2	Group B	31151	97216
19-Dec-08	3-Jan-09	Commodity 1	Group B	31770	17193
21-Dec-08	3-Jan-09	Commodity 1	Group A	32242	86601
13-Dec-08	5-Jan-09	Commodity 1	Group A	32648	84035
24-Dec-08	3-Jan-09	Commodity 1	Group A	32740	29008
20-Dec-08	3-Jan-09	Commodity 1	Group B	34756	48281
30-Dec-08	1-Jan-09	Commodity 1	Group A	35116	28034
20-Dec-08	1-Jan-09	Commodity 1	Group B	35404	5921
13-Dec-08	3-Jan-09	Commodity 1	Group B	36411	91158
12-Dec-08	5-Jan-09	Commodity 1	Group B	39168	70534
19-Dec-08	6-Jan-09	Commodity 1	Group A	39184	79575
24-Dec-08	24-Dec-08	Commodity 1	Group A	39223	20002
1-Jan-09	3-Jan-09	Commodity 1	Group A	40312	30823
19-Dec-08	5-Jan-09	Commodity 1	Group A	42744	29207
23-Nov-08	24-Nov-08	Commodity 1	Group A	43472	98013
25-Dec-08	6-Jan-09	Commodity 1	Group B	45022	14701
24-Dec-08	3-Jan-09	Commodity 1	Group B	45286	38103
2-Jan-09	6-Jan-09	Commodity 1	Group B	46646	53611
1-Jan-09	6-Jan-09	Commodity 1	Group B	47448	54816
21-Dec-08	3-Jan-09	Commodity 1	Group B	49234	54482
30-Dec-08	6-Jan-09	Commodity 1	Group B	49997	35541
16-Dec-08	5-Jan-09	Commodity 1	Group A	51473	82930
24-Dec-08	3-Jan-09	Commodity 1	Group A	52930	39388

I am trying to:

1. count number of the active account numbers that have two commodities (have both commodity 1 and commodity 2) at any given time (filtered date is between the contract start and end date)
2. filter on step 1 where the commodity group is different. (example account has two commodities, but one commodity is in group A and the other commodity is in group B)

I tried to write some DAX, and came up with a measure that give me the right count, but it's very slow. The refresh time on this measure in Card Visualization is about 2 seconds, if I trend this by year(4 years), it takes 14 seconds. 

I really need some help to optimize this DAX code. Thanks!

Here is the DAX code:

Different Commodity Group =

VAR refdate =
    MAX ( Dim_date[Date] )
VAR temptable =
    FILTER (
        SUMMARIZE (
            FILTER (
                contract_table,
                contract_table [CONTRACT_START_DATE] <= refdate
                    && contract_table [CONTRACT_END_DATE] >= refdate
            ),
            contract_table [Account Number],
           "Commodity 1 Group", CALCULATE (
                LASTNONBLANK ( contract_table [Commodity Group], 1 ),
                KEEPFILTERS ( contract_table [Commodity] = "Commodity 1" )
            ),
            " Commodity 2 Group ", CALCULATE (
                LASTNONBLANK ( contract_table [Commodity Group], 1 ),
                KEEPFILTERS ( contract_table [Commodity] = "Commodity 2" )
            )
        ),
        [Commodity 1 Group] <> [Commodity 2 Group]
            && [Commodity 1 Group] <> BLANK ()
            && [Commodity 2 Group] <> BLANK ()
    )
RETURN
    COUNTROWS ( temptable )

[AlB · Accepted solution]

Hi @reuben521 

Try this

Different Commodity Group =
VAR refdate =
    MAX ( Dim_date[Date] )
VAR temptable =
    FILTER (
        FILTER (
            contract_table,
            contract_table[CONTRACT_START_DATE] <= refdate
                && contract_table[CONTRACT_END_DATE] >= refdate
        ),
        CALCULATE (
            DISTINCTCOUNT ( contract_table[Commodity] ),
            ALLEXCEPT ( contract_table, contract_table[Account Number] )
        ) > 1
            && CALCULATE (
                DISTINCTCOUNT ( contract_table[Commodity Group] ),
                ALLEXCEPT ( contract_table, contract_table[Account Number] )
            ) > 1
    )
RETURN
    CALCULATE ( DISTINCTCOUNT( contract_table[Account Number] ), temptable )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[reuben521]

Thank you SO much, I didn't know you can filter like that, and I can add that to my arsenal right now.

The nest filter didn't work together properly and produce the result I cannot make sense of. However, when I combine them in one filter everything works.

Also, your calculate(distinctcount at the end perform well when I do the year trending, but when I created a table with account number, and your measure it becomes very slow, so I changed it to countrows(groupby(temptable, acccount number)), and everything is fast now.

The final DAX looks like this.

Different Commodity Group =
VAR refdate =
    MAX ( Dim_date[Date] )
VAR temptable =
   
   FILTER (
      contract_table,   
        CALCULATE (
            DISTINCTCOUNT ( contract_table[Commodity] ),
            ALLEXCEPT ( contract_table, contract_table[Account Number] ),
            contract_table[CONTRACT_START_DATE] <= refdate,
            contract_table[CONTRACT_END_DATE] >= refdate
        ) > 1
            && 
        CALCULATE (
            DISTINCTCOUNT ( contract_table[Commodity Group] ),
            ALLEXCEPT ( contract_table, contract_table[Account Number] ),
            contract_table[CONTRACT_START_DATE] <= refdate,
            contract_table[CONTRACT_END_DATE] >= refdate 
            ) > 1
    )
RETURN
    countrows(groupby(temptable, account number))

overall, running time on card visualization is shorten to half of the original time, and annual trending has reduced to 3 seconds from about 15 seconds before, so huge improvements.

Feel free to optimize it further if you think there is room for improvement.

thanks again, one of the best things I have learned so far.
 

[AlB]

Cool 

---

@reuben521 wrote:

Thank you SO much, I didn't know you can filter like that, and I can add that to my arsenal right now.

---

What do you mean? Filter like what?

Something else just came to mind: playing with the fact that we need the first and second distinctcount to be =2, the product must be =4 . This will only work if there are two commodities and two commodity groups (and not more):

Different Commodity Group =
VAR refdate =
    MAX ( Dim_date[Date] )
VAR temptable =   
   FILTER (
      contract_table,   
        CALCULATE (
            DISTINCTCOUNT ( contract_table[Commodity] ) * DISTINCTCOUNT ( contract_table[Commodity Group] ),
            ALLEXCEPT ( contract_table, contract_table[Account Number] ),
            contract_table[CONTRACT_START_DATE] <= refdate,
            contract_table[CONTRACT_END_DATE] >= refdate
        ) = 4
            
    )
RETURN
    countrows(groupby(temptable, account number))

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[reuben521]

What I meant was that, I didn't know you can throw in calculate() in a filter(), and perform well. 

Now, I am definitely going to use this technique in lot of my other stuff. Thanks Again.

I gave your new measure a try, I think mine is slightly faster like within 100ms on a card visualization.

I think I will stick with my version for now, but I think yours is just just as good.

Thanks,