Better model to improve the performance calculated table/Dax measures

[sivarajan21]

Hi Team,

Now, I have an issue with Data model I have developed. Attached is the model i use for my power bi reports that contains 3 dashboards. Invoice quality is one of the dashboards.

Below are the requirements to be created for visuals:
For the Data[DBName-Point_Id] & Data[Date] columns from Data table, we need to find the status(Dax) as below:
*Credit Cancels Invoice -- Number of Credits = Number of Invoices && net cost = 0
*Invoice-Credit Value Mismatch -- ISEVEN(Net invoices) && Net cost <>0
*Multiple Credits -- Number of Credits > Number of Invoices && Net cost <>0
*Multiple Invoices -- Number of invoices >1 && Number of invoices >Number of Credits

We need to use this Status dax in visual y-axis of stacked bar chart and use the Count of Dates from Data table as x-axis:

Number of Credits,Number of invoices, Net invoices and Net cost can dax or a calculated table columns.

Currently, we have created a calculated table using dax logic as below:
Based on the above table, Status calculated column in created in same table:

Invoice Issues =
CALCULATETABLE (
SUMMARIZE (
Data,
Data[DBName-Point_Id],
Data[Date],
"Invoice Issue", SUM ( Data[Invoice / Credit] ),
"Number of Invoices", CALCULATE ( COUNT ( Data[Invoice / Credit] ), Data[Invoice / Credit] > 0 ),
"Number of Credits", CALCULATE ( COUNT ( Data[Invoice / Credit] ), Data[Invoice / Credit] < 0 ),
"Net Cost", SUM ( Data[Cost] )
),
Data[Source] = "Invoice"
)

Status =
IF (
'Invoice Issues'[Number of Invoices] > 1
&& 'Invoice Issues'[Number of Invoices] > 'Invoice Issues'[Number of Credits],
"Multiple Invoices",
IF (
'Invoice Issues'[Number of Credits] > 'Invoice Issues'[Number of Invoices]
&& 'Invoice Issues'[Net Cost] <> 0,
"Multiple Credits",
IF (
ISEVEN ( 'Invoice Issues'[Net Invoices] )
&& 'Invoice Issues'[Net Cost] <> 0,
"Invoice-Credit Value Mismatch",
IF (
'Invoice Issues'[Number of Credits] = 'Invoice Issues'[Number of Invoices]
&& 'Invoice Issues'[Net Cost] = 0,
"Credit Cancels Invoice",
BLANK ()
)
)
)
)

This calculated table is then connected to Points and calendar table.
DBName-Point_id(points table) --DBName-Point_id(Invoice Issues table)
Date (Calendar table) -- Date (Invoice Issues table)
This setup causes performance issues especially Status column. Is there any better ways to model this to improve the performance?

please let me know if you need further info!

Thanks in advance!
@marcorusso @danextian @tharunkumarRTK 

[v-nmadadi-msft]

Hi @sivarajan21 ,

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. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @sivarajan21 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-nmadadi-msft · Accepted solution]

Hi @sivarajan21 ,
Thanks for reaching out to the Microsoft fabric community forum.

As mentioned by @speedramps using star schema is the best approach to optimize data models.
Other steps you can take to optimize the data model is:
1) Rather than precomputing the tables using a calculated table, consider using dynamic DAX measures that respond to the current filter context. This approach enhances report interactivity, reduces memory overhead, and significantly improves model performance by calculating only what's needed, when it's needed.
2) Also for transformations that don't need to respond to user interactions, consider performing them in Power Query. This can offload processing from DAX and improve overall performance.
3) Additionally, Regularly check your model's size and optimize by removing unnecessary columns or tables. Tools like the Power BI Performance Analyzer can help identify bottlenecks.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you

[speedramps]

Are you sharing real data data? It contains customer names and addresses, which may get you into trouble.

You data model has no structure. It is what we call a spagetti model.
Consider using a start schema

learn how here

https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

https://en.wikipedia.org/wiki/Star_schema

https://www.youtube.com/watch?v=vZndrBBPiQc

[lawleitl]

You have to try using Power BI’s calculation groups instead of multiple calculated columns or heavy DAX logic in the model. This can offload some logic to runtime and help with performance. Also, filter your data model or create a separate summary table for visuals to reduce processing overhead.