topic Re: Dynamic Segmentation performance slow in a matrix visual in DAX Commands and Tips

Dynamic Segmentation performance slow in a matrix visual

learningOh — Mon, 04 Dec 2023 09:28:00 GMT

Hi all,

i have a DAX measure that tags a status to the my customers.

Acct WinLoss = Var cy = [Rev Current Year W/0 Scale] var py = [Rev Prior Year W/0 Scale] var ccy = [Count of Non-Zero Revenue Months] var pcy = [Count of Non-Zero Revenue Months PY] var cm = month(max('Calendar'[calendar_date])) return switch( true(), cy >= 1000 && py = 0 && ccy>=2, "Acct Win", cm = 10 && ccy =1 && py = 0 && cy >= 1000, "Acct Win", cy=0 && py>= 1000 && pcy >=2, "Acct Loss", cy >= 1000 && cy>=2 && py >= 1000 && pcy >=2, "Acct Retained", cm = 10 && cy >= 1000 && ccy>=1 && py >= 1000 && pcy >=2, "Acct Retained", BLANK() )

To further add, the measures for

Rev Current Year W/0 Scale =

var fy = MAX('Calendar'[Fiscal_Year])

return

CALCULATE(SUM('InMarket Sales'[USD @ Latest FY Bud]), FILTER('InMarket Sales', [Fiscal Year]= fy))

and

Count of Non-Zero Revenue Months =

var fy= MAX('Calendar'[Fiscal_Year]) return

COUNTROWS(

FILTER(

SUMMARIZE(

FILTER('InMarket Sales', [fiscal year] = fy),

'Calendar'[calendar date day 1],

"NonZeroRevenue", SUM('InMarket Sales'[USD @ Latest FY Bud])

[NonZeroRevenue] > 0

)

in a Matrix Visual, i have the following columns

product category | country | product group | customer name | count (Distinct) of customer name | Rev Current Year W/O Scale

i filter the matrix visual by placing the Acct WinLoss in and choosing the filter to contain "Win".

that way only my won accounts are seen. but the matrix visual is taking a long time to load. can anyone help me improve the visual loading performance? i used the performance analyser and it says that the DAX Query is the one causing the performance to be slow.
The number of account wins and losses can go up to around 200-300 accounts.

Re: Dynamic Segmentation performance slow in a matrix visual

Fowmy — Mon, 04 Dec 2023 09:54:40 GMT

learningOh

Please try this meaure. Take a look at your model and the other measures. This article will be helful:
Optimization guide for Power BI - Power BI | Microsoft Learn

Count of Non-Zero Revenue Months = VAR fy = MAX ( 'Calendar'[Fiscal_Year] ) RETURN COUNTROWS ( FILTER ( ADDCOLUMNS ( SUMMARIZE ( FILTER ( 'InMarket Sales', [fiscal year] = fy ), 'Calendar'[calendar date day 1] ), "NonZeroRevenue", SUM ( 'InMarket Sales'[USD @ Latest FY Bud] ) ), [NonZeroRevenue] > 0 ) )

Re: Dynamic Segmentation performance slow in a matrix visual

learningOh — Tue, 05 Dec 2023 03:36:06 GMT

Thank you for sharing this approach. it helped to cut down the time needed to load. is there any other method to further improve? the loading time is still slow

Re: Dynamic Segmentation performance slow in a matrix visual

Fowmy — Tue, 05 Dec 2023 05:07:09 GMT

learningOh

Optimizing the performance of a Power BI file requires a closer examination of the data model and measures. Without specific details, it's challenging to provide precise solutions. Various factors impact performance, such as data model complexity, connectivity mode (Import/DQ/AS), inefficient DAX measures, or suboptimal relationships. To offer tailored advice, I'd need more insights into your setup. Feel free to share specific aspects, and I can guide you more effectively.