DAX performance optimization

[Nicpet0]

Dear community,

I am seeking help optimizing a Power BI report that is experiencing performance issues, which I suspect may be related to my DAX measure included below. The report is designed to create an income statement (P&L).

The model is built as a composite model, where:

• The primary data source is a Tabular semantic model running in DirectQuery mode.
• Additional supporting tables are imported from SharePoint Lists.

I have also attached a screenshot of the model setup, where the imported SharePoint tables are connected to the Tabular model through G/L Account fields.

In the report, I have built a Matrix visual with the following hierarchy:

1. Category (from the Category SharePoint List)
2. Subcategory (from the G/L Account SharePoint List)
3. G/L Account (from the G/L Account SharePoint List)

Some of the categories are manually constructed because they do not exist natively in the Tabular model, but are required to create a complete P&L structure. To support this, I created indexed categories in the SharePoint List, which are then referenced in the DAX logic to calculate the corresponding category values.

The current setup performs well in Power BI Desktop, where the matrix typically loads within approximately two seconds. However, after publishing to the Power BI Service, the same visual takes significantly longer to load.

I am currently trying to isolate the root cause and determine whether the issue is related to my DAX logic, the composite model setup, or the interaction between DirectQuery and imported SharePoint tables. The measure itself appears fairly complex, so I would appreciate guidance on whether there are opportunities to optimize or redesign the DAX for better performance.

ACT = 
VAR _Category =
    SELECTEDVALUE('G L Account Category SharePoint'[G L Account Category])
VAR _IsDetailLevel =
    ISINSCOPE('G L Account SharePoint'[G L Account Subcategories])
    || ISINSCOPE('G L Account SharePoint'[G L Account])
RETURN
IF(
    _IsDetailLevel,
    -[Amount],
    SWITCH(
        _Category,
        "Gross Profit",
            CALCULATE(
                -[Amount],
                REMOVEFILTERS('G L Account Category SharePoint'),
                'G L Account Category SharePoint'[Index] < 3
            ),
        "Total Overheads",
            CALCULATE(
                -[Amount],
                REMOVEFILTERS('G L Account Category SharePoint'),
                'G L Account Category SharePoint'[Index] > 3,
                'G L Account Category SharePoint'[Index] < 7
            ),
        "Total Personnel Costs",
            CALCULATE(
                -[Amount],
                REMOVEFILTERS('G L Account Category SharePoint'),
                'G L Account Category SharePoint'[Index] > 7,
                'G L Account Category SharePoint'[Index] < 10
            ),
        "EBITDA",
            CALCULATE(
                -[Amount],
                REMOVEFILTERS('G L Account Category SharePoint'),
                'G L Account Category SharePoint'[Index] < 13
            ),
        "EBIT",
            CALCULATE(
                -[Amount],
                REMOVEFILTERS('G L Account Category SharePoint'),
                'G L Account Category SharePoint'[Index] < 15
            ),
        -[Amount]
    )
)

[v-pnaroju-msft]

Hi @Nicpet0,

Thank you for the update.
Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

[v-pnaroju-msft]

Hi @Nicpet0,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Thankyou, @johnt75 for your response.

Hi @Nicpet0,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solution provided by @johnt75  to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[johnt75 · Accepted solution]

The first thing I would try is to incorporate the sharepoint files into the main semantic model. The performance of a model in import mode is always going to be better than a composite model running partly in DQ. If you don't control the main semantic model yourself, have a word with whoever does and see if they can include the sharepoint files for you. You could even give them TMDL files to create the tables and relationships, set display properties etc.

If including the sharepoint files in the main model isn't possible then there are a couple of things I can think to try, but I don't know whether they will have a significant impact on performance. Firstly, rather than specifying ranges of values for 'G L Account Category SharePoint'[Index] try specifying actual values - so rather than > 7 and <= 10, specify { 8, 9, 10 } with the IN operator.

Secondly, you could try and create the filter for 'G L Account' manually rather than relying on the limited relationship. If there was a Key column in both 'G L Account' and 'G L Account Sharepoint' you could try something like

ACT =
VAR _Category =
    SELECTEDVALUE ( 'G L Account Category SharePoint'[G L Account Category] )
VAR _IsDetailLevel =
    ISINSCOPE ( 'G L Account SharePoint'[G L Account Subcategories] )
        || ISINSCOPE ( 'G L Account SharePoint'[G L Account] )
VAR GrossProfitFilter =
    TREATAS (
        CALCULATETABLE (
            VALUES ( 'G L Account Sharepoint'[Key] ),
            'G L Account Category SharePoint'[Index] IN { 0, 1, 2 }
        ),
        'G L Account'[Key]
    )
VAR TotalOverheadsFilter =
    TREATAS (
        CALCULATETABLE (
            VALUES ( 'G L Account Sharepoint'[Key] ),
            'G L Account Category SharePoint'[Index] IN { 4, 5, 6 }
        ),
        'G L Account'[Key]
    )
RETURN
    IF (
        _IsDetailLevel,
        - [Amount],
        SWITCH (
            _Category,
            "Gross Profit",
                CALCULATE (
                    - [Amount],
                    REMOVEFILTERS ( 'G L Account Category SharePoint' ),
                    GrossProfitFilter
                ),
            "Total Overheads",
                CALCULATE (
                    - [Amount],
                    REMOVEFILTERS ( 'G L Account Category SharePoint' ),
                    TotalOverheadsFilter
                )
        )
    )

It might also be worth examining the [Amount] measure to see if there are performance tweaks you could make in there.

[Nicpet0]

Hi @johnt75,

Thank you for the reponse. I believe the next thing to try is, as you mentioned, including the information from SharePoint Lists into the base model.

Thank you!

[v-pnaroju-msft]

Hi @Nicpet0,

We are following up to confirm whether the information shared above has helped resolve your issue. If you require further assistance, please feel free to reach out to the Microsoft Fabric community.

Thank you.

[pankajnamekar25]

Hello @Nicpet0 

There are multiple area where we can imporve perfromance.

try this measure

ACT =
VAR _IsDetailLevel =
ISINSCOPE ( 'G L Account SharePoint'[G L Account Subcategories] )
|| ISINSCOPE ( 'G L Account SharePoint'[G L Account] )
VAR _BaseAmount = -[Amount]
RETURN
IF (
_IsDetailLevel,
_BaseAmount,
VAR _Category = SELECTEDVALUE ( 'G L Account Category SharePoint'[G L Account Category] )
RETURN
SWITCH (
_Category,
"Gross Profit",
CALCULATE (
-[Amount],
KEEPFILTERS (
FILTER (
ALL ( 'G L Account Category SharePoint' ),
'G L Account Category SharePoint'[Index] < 3
)
)
),
"Total Overheads",
CALCULATE (
-[Amount],
KEEPFILTERS (
FILTER (
ALL ( 'G L Account Category SharePoint' ),
'G L Account Category SharePoint'[Index] > 3
&& 'G L Account Category SharePoint'[Index] < 7
)
)
),
"Total Personnel Costs",
CALCULATE (
-[Amount],
KEEPFILTERS (
FILTER (
ALL ( 'G L Account Category SharePoint' ),
'G L Account Category SharePoint'[Index] > 7
&& 'G L Account Category SharePoint'[Index] < 10
)
)
),
"EBITDA",
CALCULATE (
-[Amount],
KEEPFILTERS (
FILTER (
ALL ( 'G L Account Category SharePoint' ),
'G L Account Category SharePoint'[Index] < 13
)
)
),
"EBIT",
CALCULATE (
-[Amount],
KEEPFILTERS (
FILTER (
ALL ( 'G L Account Category SharePoint' ),
'G L Account Category SharePoint'[Index] < 15
)
)
),
_BaseAmount
)
)

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.

Thanks,

Connect with me on:
LinkedIn |
Data With Pankaj - YouTube

[Nicpet0]

@pankajnamekar25 

Thanks for the response! Can you explain to me how this new measure is better than my current?

Thanks.

[pankajnamekar25]

Hello @Nicpet0 

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.

Thanks,

Connect with me on:
LinkedIn |
Data With Pankaj - YouTube

[Nicpet0]

@pankajnamekar25 

Firstly, the code you send did not work. Secondly, i am 99% sure your responses comes from ChatGPT. In your latest post, some of the explanations does not make sense at all. 

For example this - Typo fixed in Total Personnel Costs branch (ShareSheet → SharePoint) - There is no typo at all in the code. Sorry this was not helpful