Table visual takes 20 - 30 minutes to load because of DAX measures

[Uthraa92]

Hi all,

Need you thoughts to optimize the DAX query!!!!

I need a PowerBI DAX query that identifies offset amounts that net off like -10 and 10 across two entries for same number. If it is different numbers with -10 and 10 entries, then it is not offset. We will need to create a flag as below,

• 'Yes' if the amount between two entries nets off for the same number
• 'No' if there is no amount to net off within same number
• 'Yes (does not net off)' if the amount between two entries nets off for the same number but there is additional entry with same amount.

The Flag updated as an example in the below table (please note that we have other columns in the base table that I need to consider and I am showcasing only the critical column for this flag calculation):

Number	Amount	Offset_Flag
1001	50	No
1001	35	Yes
1001	-35	Yes
1001	150	No
2011	10	Yes (does not net off)
2011	-10	Yes (does not net off)
2011	10	Yes (does not net off)
5111	2.5	Yes
5111	-2.5	Yes

For now, below is the technique that I have followed:

1. Created Absolute value for the amount and concatenated with number.
2. Then had a measure to flag (flag_1) if there is amount for the same number with the opposite sign or not, as follows

Number	Amount	Number_ABSAmount	Flag_1
1001	50	1001-50	No
1001	35	1001-35	Yes
1001	-35	1001-35	Yes
1001	150	1001-150	No
2011	10	2011-10	Yes
2011	-10	2011-10	Yes
2011	10	2011-10	Yes
5111	2.5	5111-2.5	Yes
5111	-2.5	5111-2.5	Yes

Created a measure (Oppcount) to calculate the count of opposite sign for the same number (how many times the same amount with opposite sign is repeating) and another measure (Samecount) to calculate the count of same sign for the same number (how many times the same amount with same sign is repeating). Then check if the Flag_1 is “No” then count will always be 1 and hence the Offset_flag can be marked as “No”. If the Flag_1 is “Yes” and count is same for both the Oppcount and Samecount then it means that it is netting off amounts for the same number and hence Offset_flag can be marked as “Yes”. If the Flag_1 is “Yes” and count does not match between the Oppcount and Samecount then it means that it is not netting off amount and so Offset_flag can be marked as “Yes (does not net off)”

Number	Amount	Number_ ABSAmount	Flag_1	OppCount	SameCount	Offset Flag
1001	50	1001-50	No	1	1	No
1001	35	1001-35	Yes	1	1	Yes
1001	-35	1001-35	Yes	1	1	Yes
1001	150	1001-150	No	1	1	No
2011	10	2011-10	Yes	1	2	Yes (does not net off)
2011	-10	2011-10	Yes	2	1	Yes (does not net off)
2011	10	2011-10	Yes	1	2	Yes (does not net off)
5111	2.5	5111-2.5	Yes	1	1	Yes
5111	-2.5	5111-2.5	Yes	1	1	Yes

Also, I have slicer filters in the previous pages in dashboard which will have impact in this entries and flagging. Below are the measures created

OppCount Measure =

VAR CurrentNumber = MAX(Table[Number])

VAR CurrentAmount = MAX(Table[Amount])

VAR OppositeAmountExists = CALCULATE(

COUNTROWS( FILTER(

ALLSELECTED(Table),

 Table[Number] = CurrentNumber && Table[Amount] = -CurrentAmount

) ) )

RETURN OppositeAmountExists

SameCount Measure =

VAR CurrentNumber = MAX(Table[Number])

VAR CurrentAmount = MAX(Table[Amount])

VAR SameAmountExists = CALCULATE(

COUNTROWS( FILTER(

ALLSELECTED(Table),

 Table[Number] = CurrentNumber && Table[Amount] = CurrentAmount

) ) )

RETURN SameAmountExists

Offset_flag = IF(Table[Flag_1]="Yes" && Table[OppCount Measure]= Table[SameCount Measure],"Yes",IF(Table [Flag_1]="Yes" && Table[OppCount Measure]<> Table [SameCount Measure],"Yes (does not net off)","No")

Everything is working perfectly fine. But the huge problem is I have data will involves millions of entries/line items and this process takes 20 -30 mins everytime. And, I have filter for this Offset_Flag and after every selection it takes around 30 mins or ends up with an error because of too much processing time. Any better approach for flagging the Offsetting amounts using DAX or any way of optimizing the existing DAX query so that the loading time for this table visual takes only couple of  minutes ?

I have to complete this by this week. Any advice to sort this out will be really appreciated!! Thanks in advance.

[Uthraa92]

Thanks @Greg_Deckler 

I have updated the query into single measure, removing the calculate and using the switch function (but had to use IF function too) but it still take 30 minutes. Is this the best way possible ?

CombinedMeasure =

VAR CurrentNumber = MAX(Table[Number])

VAR CurrentAmount = MAX(Table[Amount])

VAR OppositeAmountExists = COUNTROWS(

        FILTER(

            ALLSELECTED(Table),

            Table[Number] = CurrentNumber && Table[Amount] = -CurrentAmount

        )

    )

VAR SameAmountExists = COUNTROWS(

        FILTER(

            ALLSELECTED(Table),

            Table[Number] = CurrentNumber && Table[Amount] = CurrentAmount

        )

    )

VAR IsFlag_1 = Table[Flag_1] = "Yes"

VAR IsOppCountEqualSameCount = OppositeAmountExists = SameAmountExists

VAR ConditionKey =

    CONCATENATE(

        IF(IsFlag_1, "Yes", "No"),

        IF(IsOppCountEqualSameCount, "_NetOff", "_DoesNotNetOff")

    )

RETURN

    SWITCH(

        ConditionKey,

        "Yes_NetOff", "Yes (Net Off)",

        "Yes_DoesNotNetOff", "Yes (Does not Net Off)",

        "No"

    )

[Greg_Deckler]

@Uthraa92 It's almost impossible to troubleshoot this without a sample dataset. 

[Greg_Deckler]

@Uthraa92 Couple things here. One, your CALCULATE is useless in SameCount Measure and OppCount Measure so you can remove it. Second, in your Offset_flag measure, switch to using a SWITCH( TRUE(), ... ) statement and make sure that you have your most common scenario as the very first condition, second most common the 2nd, etc. Finally, I have seen tremendous performance improvements by consolidating all of the code into a single measure. So if you don't need to visualize those intermedidate measures, ditch them and incorporate the code into a single measure. 

Performance Tuning DAX - Part 1 - Microsoft Fabric Community