Forum Discussion

stribor45's avatar
stribor45
Post Prodigy
5 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 visual loads forever so I run performance analyzer and then run DAx in DAX studio which helpoed me isolate some measures that are slow. My matrix is kind of designed this way

 

Rows are Country, State and City from Table_A

Columns are 

Approval_2026, (measures related to this column are fast)

Rejections_2026 (measures related to this column are fast)

Approval_2025, (measures related to this column are slow)

Rejections_2025 (measures related to this column are slow)

...

---

I think problem is that for measures relaated to 2026 need to get geo data from Table_A but 2025 from Table_B while keeping the matrix rows with Country,State, City from Table_A.  Measures (not 2026)I have currently are very much similar to each oother and was hoping for some advice how to speed this up. 

 

CALCULATE (
    [Approvals_2025],
    
    REMOVEFILTERS (Table_A),

    TREATAS ( VALUES ( Table_A[Country]), Table_B[Country]),
    TREATAS ( VALUES ( Table_A[State]), Table_B[State] ),
    TREATAS ( VALUES ( Table_A[City] ), Table_B[City] )
)

 

  • 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

5 Replies

  • 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

  • mizan2390's avatar
    mizan2390
    Super User
    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

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    4 months ago

    Hi stribor45 

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.


    Thank you.

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    4 months ago

    Hi stribor45 

    May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.


    Thank you