Forum Discussion

stribor45's avatar
stribor45
Post Prodigy
4 months ago
Solved

Optimize Measures

I have page with matrix visual that has about 15 columns. I inherited this report and rcently new requirement came up which I made it work however measures I made are super slow. I noticed that visua...
  • mizan2390's avatar
    mizan2390
    4 months ago
    The reason your measure is super slow is that you are using three separate TREATAS functions with VALUES for each column.
    By applying TREATAS independently to Country, State, and City, you are forcing the DAX engine to evaluate the Cartesian product (crossjoin) of all visible values in those columns. This means DAX is evaluating combinations of geographies that do not actually exist in the real world (e.g., combining a City from Texas with the State of New York), which creates a massive and inefficient filter for the storage engine to process.
    Can you try this DAX, whether this solves your problem?
    Approvals_2025 = 
CALCULATE (
    [Approvals_2025],
    REMOVEFILTERS ( Table_A ),
    TREATAS ( 
        -- Creates a table of only valid, existing combinations
        SUMMARIZE ( 
            Table_A, 
            Table_A[Country], 
            Table_A[State], 
            Table_A[City] 
        ),
        -- Maps them directly to the corresponding columns in Table_B
        Table_B[Country], 
        Table_B[State], 
        Table_B[City] 
    )
)

    While the TREATAS DAX solution (a "virtual relationship") will improve performance, virtual relationships are resolved entirely at query time and do not benefit from the internal structures and indexes the engine uses to optimize physical relationships.

    For the best possible performance, If its permit in you model, you should eliminate the virtual relationship and rely on a Star Schema data model.

    1. Create a new, dedicated Geography Dimension Table that contains a distinct list of all unique Country, State, and City combinations.
    2. Create a physical one-to-many relationship between this new Geography table and Table_A.
    3. Create a physical one-to-many relationship between the Geography table and Table_B. 
    4. Use the Country, State, and City columns from the new Geography table on the rows of your matrix

    If this helped, please consider giving kudos and mark as a solution

    mein replies or I'll lose your thread

Kedar_Pande's avatar
Kedar_Pande
Super User
4 months ago

stribor45 

Replace 3 TREATAS calls with 1 SUMMARIZE bridge:

Bridge_2025 = 
SUMX(
SUMMARIZE(Table_A, Table_A[Country], Table_A[State], Table_A[City]),
CALCULATE([Base_2025])
)

Approval_2025 = [Bridge_2025]
Rejection_2025 = [Bridge_2025]

 

 

stribor45's avatar
stribor45
Post Prodigy
4 months ago

sorry I made typos in my original code snipped. I adjusted it now