Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
reuben521
Frequent Visitor

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

‎10-09-2019 09:12 AM

Hi,

 

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

 

CONTRACT_START_DATECONTRACT_END_DATECommodityCommodity GroupAccount NumberSite ID
3-Jan-096-Jan-09Commodity 1Group A113195821
25-Dec-0825-Dec-08Commodity 2Group B123740414
2-Jan-092-Jan-09Commodity 1Group A123769001
2-Jan-092-Jan-09Commodity 1Group A123774005
31-Dec-081-Jan-09Commodity 1Group A20171565
3-Jan-096-Jan-09Commodity 1Group A206860490
30-Dec-081-Jan-09Commodity 1Group A457940338
30-Dec-086-Jan-09Commodity 1Group A508678650
31-Dec-086-Jan-09Commodity 1Group A697177416
18-Dec-086-Jan-09Commodity 1Group B1020329024
31-Dec-083-Jan-09Commodity 1Group B1027727474
3-Jan-093-Jan-09Commodity 2Group B1111668412
24-Dec-0831-Dec-08Commodity 1Group B1425735815
25-Dec-083-Jan-09Commodity 1Group A1527475172
24-Dec-083-Jan-09Commodity 1Group A1528478766
3-Jan-096-Jan-09Commodity 1Group A1541282916
31-Dec-081-Jan-09Commodity 1Group B1563779614
1-Jan-097-Jan-09Commodity 1Group B1752091613
11-Dec-085-Jan-09Commodity 1Group A192601617
4-Jul-084-Jul-08Commodity 1Group A2058992557
6-Dec-081-Jan-09Commodity 1Group B2180699768
25-Dec-085-Jan-09Commodity 1Group A2199745567
20-Dec-086-Jan-09Commodity 1Group B22596866
4-Jan-094-Jan-09Commodity 1Group A2365424111
5-Dec-084-Jan-09Commodity 2Group B2379685511
31-Dec-083-Jan-09Commodity 1Group B2442693451
17-Dec-085-Jan-09Commodity 1Group A2469613947
31-Dec-083-Jan-09Commodity 1Group A2503956467
13-Dec-0813-Dec-08Commodity 1Group B2542927841
1-Jan-097-Jan-09Commodity 1Group B2799119861
21-Dec-083-Jan-09Commodity 1Group B2914692076
2-Jan-092-Jan-09Commodity 1Group A2925540446
18-Dec-086-Jan-09Commodity 1Group A30873725
11-Dec-0811-Dec-08Commodity 2Group B3115197216
19-Dec-083-Jan-09Commodity 1Group B3177017193
21-Dec-083-Jan-09Commodity 1Group A3224286601
13-Dec-085-Jan-09Commodity 1Group A3264884035
24-Dec-083-Jan-09Commodity 1Group A3274029008
20-Dec-083-Jan-09Commodity 1Group B3475648281
30-Dec-081-Jan-09Commodity 1Group A3511628034
20-Dec-081-Jan-09Commodity 1Group B354045921
13-Dec-083-Jan-09Commodity 1Group B3641191158
12-Dec-085-Jan-09Commodity 1Group B3916870534
19-Dec-086-Jan-09Commodity 1Group A3918479575
24-Dec-0824-Dec-08Commodity 1Group A3922320002
1-Jan-093-Jan-09Commodity 1Group A4031230823
19-Dec-085-Jan-09Commodity 1Group A4274429207
23-Nov-0824-Nov-08Commodity 1Group A4347298013
25-Dec-086-Jan-09Commodity 1Group B4502214701
24-Dec-083-Jan-09Commodity 1Group B4528638103
2-Jan-096-Jan-09Commodity 1Group B4664653611
1-Jan-096-Jan-09Commodity 1Group B4744854816
21-Dec-083-Jan-09Commodity 1Group B4923454482
30-Dec-086-Jan-09Commodity 1Group B4999735541
16-Dec-085-Jan-09Commodity 1Group A5147382930
24-Dec-083-Jan-09Commodity 1Group A5293039388

 

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 )

 

 

Labels:
Message 1 of 5
705 Views
0
1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

‎10-09-2019 10:40 AM

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  Datanaut

View solution in original post

Message 2 of 5
671 Views
0
4 REPLIES 4
AlB
Community Champion
Community Champion

‎10-09-2019 10:40 AM

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  Datanaut

Message 2 of 5
672 Views
0
reuben521
Frequent Visitor
In response to AlB

‎10-09-2019 03:26 PM

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.
 

Message 3 of 5
635 Views
0
AlB
Community Champion
Community Champion
In response to reuben521

‎10-09-2019 04:24 PM

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  Datanaut

Message 4 of 5
626 Views
0
reuben521
Frequent Visitor
In response to AlB

‎10-10-2019 09:08 AM

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,

Message 5 of 5
563 Views
0

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Kudoed Authors
View All