DAX for evaluating flags in large table

[mkinde]

I have a dashboard with the following limitations:

• Over 40 million rows in the main fact table.
• The dashboard compares two time periods that are selected by the user. The time periods are internally defined (not dates) and are selected by the user using two slicers.
• There is some "same store" logic which requires the use of an IF statement in the majority of measures. Other measures are built on top of these base measures. This approach uses a pre-calculated pattern of time periods assigned to each store and joined to the main fact table.

Example measure:

measure_1 = 
VAR PeriodSelection = SELECTEDVALUE( same_store_filter[Value] )
VAR Result = 
    IF( PeriodSelection = "All Stores", 
        CALCULATE (
            SUM( fact_performancesummary[unitcapacitycnt] ),
            fact_performancesummary[resettimingdesc] IN VALUES ( compare_case_1[resettimingdesc] )
        ),
        CALCULATE (
            SUM( fact_performancesummary[unitcapacitycnt] ),
            fact_performancesummary[resettimingdesc] in VALUES(compare_case_1[resettimingdesc]),
            dim_same_store_status[resettimingdesc_2] in VALUES(compare_case_2[resettimingdesc])
        )
    )
RETURN Result

Problem:

• One of the tabs on the dashboard is essentially a large, very granular data dump with a calculation that compare values for the two time periods and evaluates them as "Added" (new products), "Deleted" (removed products), or "Maintained" (products in both time periods).
• If I use an IF or SWITCH statement for this secondary evaluation, the visual chokes on anything over a few thousand rows. However, if I use a DIVIDE statement with the two values, I can create a conditionally formatted icon that displays close to 500K rows. (The conditional formatting is based on the three states resulting from the DIVIDE: 1) a positive number; 2) a negative #1 (the error default), and 3) blank or zero.
• The user wants to be able to export an actual text flag but the conditional formatting just downloads as the underlying numeric results.

Example:

_flag = DIVIDE([measure_1],[measure_2],-1)

Conditional Formatting:

Visual:

I'm looking for ways to efficiently display a text-based flag that the user can export. 

[v-hashadapu · Accepted solution]

Hi @mkinde , Thank you for reaching out to the Microsoft Community Forum.

We find the answer posted by @lbendlin appropriate for your issue. Making it more detailed, the issue you're facing arises from the cost of evaluating conditional logic per row in visuals, especially when you're stacking multiple measures and using filtering patterns like IN. This causes performance degradation at scale. To solve this without compromising performance or exportability, follow one of these structures based on your scenario:

If the flag logic can be applied before user interaction (for example, if time periods are fixed or predictable), push it into Power Query. This approach is fully exportable and incurs no runtime cost:

if [Current Capacity] = null and [Prior Capacity] <> null then "Deleted"

else if [Current Capacity] <> null and [Prior Capacity] = null then "Added"

else "Maintained"

If the logic depends on slicers and must be calculated live, use TREATAS instead of IN. This efficiently passes filter context and avoids nested IFs:

Flag =
VAR Period1 = VALUES(compare_case_1[resettimingdesc])
VAR Period2 = VALUES(compare_case_2[resettimingdesc])
VAR Current = CALCULATE(SUM(fact_performancesummary[unitcapacitycnt]), TREATAS(Period1, fact_performancesummary[resettimingdesc]))
VAR Prior = CALCULATE(SUM(fact_performancesummary[unitcapacitycnt]), TREATAS(Period2, dim_same_store_status[resettimingdesc_2]))
RETURN
SWITCH
( TRUE(),
ISBLANK(Current) && NOT ISBLANK(Prior), "Deleted",
NOT ISBLANK(Current) && ISBLANK(Prior), "Added",
"Maintained"
)

If you're already using a performant _flag = DIVIDE([measure_1], [measure_2], -1) pattern to drive icons and just need an exportable version, avoid reintroducing complex logic. Simply map the _flag value to a text column or measure:
ExportFlag =
 SWITCH(
TRUE(), [_flag] = -1, "Deleted",
[_flag] > 0, "Maintained",
ISBLANK([_flag]) || [_flag] = 0, "Added"
)

This keeps performance intact and provides the user with a clean, exportable text-based flag.

If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[mkinde]

This has really helped me understand some of the underlying issues. The visuals are faster and have more rows available to the user. Thanks!

[lbendlin]

1. use variables, but they are mainly useful if they are referenced more than once in a query plan.

2. Compare the "IN"  performance with the "TREATAS"  performance

3. Remember the 5/50K/1M limits.

[mkinde]

The user is definitely not using the tool as designed. I was able to use your info and pair it with the CST. Thanks!