Forum Discussion

learningOh's avatar
learningOh
Frequent Visitor
2 years ago

Dynamic Segmentation performance slow in a matrix visual

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. 

3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    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
        )
    )

     

    • learningOh's avatar
      learningOh
      Frequent Visitor
      2 years ago

      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

      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

        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.