Performance issue in Workspace

[harirao]

Hi All,

Please note, I am facing performance issue, can you please help me on this, for below matrix table.

Below mentioned Measure's used in Matrix Table:

%6week_3 = IF(ISBLANK(CALCULATE(DISTINCTCOUNT(Query1[NxOpportunityIdentifier]), FILTER(Query1, Query1[actual_visibility_weeks]>=6))),0,
CALCULATE(DISTINCTCOUNT(Query1[NxOpportunityIdentifier]), FILTER(Query1, Query1[actual_visibility_weeks]>=6)))

> 6 Weeks Visibility % of Regional Total = DIVIDE(Query1[%6week_3],[Total Deals],0)

test_Wow2 = Query1[> 6 Weeks Visibility % of Regional Total]-[Test WOW2]

WOWTrend2 = IF([test_Wow2]<0,"▼",IF([test_Wow2]>0,"▲","--"))

<6 Week_No_Visibility_#Deals =
var selwek = ALLSELECTED(Condition1to5[<6 wks Visibility])
return
IF(ISBLANK(CALCULATE(DISTINCTCOUNT(Query1[OpportunityId]),
FILTER(ALL(Query1[actual_visibility_Weeks]), Query1[actual_visibility_Weeks]in selwek))),0,
CALCULATE(DISTINCTCOUNT(Query1[OpportunityId]),
FILTER(ALL(Query1[actual_visibility_Weeks]), Query1[actual_visibility_Weeks]in selwek)))

<6 week% = DIVIDE(Query1[<6 Week_No_Visibility_#Deals],Query1[%6week_1&2],0)

%6week_1&2 = CALCULATE(DISTINCTCOUNT(Query1[NxOpportunityIdentifier]), FILTER(Query1, Query1[actual_visibility_weeks]> 0))
+
CALCULATE(DISTINCTCOUNT(Query1[NxOpportunityIdentifier]), FILTER(Query1, Query1[0 visibility exclusions]= "No"),
FILTER(Query1, Query1[actual_visibility_weeks]= 0),
FILTER(ALL(Query1),Query1[1MUSD Flag]=1 && Query1[PipelineExclusionIndicator]="N"))

Test WOW4 = VAR prv_wk =DATEADD(Query1[reportdate],-7,DAY)
Return
CALCULATE([<6 week%], FILTER(ALL(Query1),Query1[reportdate] in prv_wk &&
Query1[subregion]=SELECTEDVALUE(Query1[subregion])),
FILTER(ALL(Query1),Query1[1MUSD Flag]=1 && Query1[PipelineExclusionIndicator]="N"))

WOWTrend4 = IF([test_Wow4]<0,"▼",IF([test_Wow4]>0,"▲","--"))

No_Visibility_#Deals = IF(ISBLANK(CALCULATE(DISTINCTCOUNT(Query1[OpportunityId]),
FILTER(Query1, Query1[actual_visibility_Weeks]=0),
FILTER(Query1, Query1[0 visibility exclusions]="No"),
FILTER(ALL(Query1),Query1[1MUSD Flag]=1 && Query1[PipelineExclusionIndicator]="N")
)),0,
CALCULATE(DISTINCTCOUNT(Query1[OpportunityId]),
FILTER(Query1, Query1[actual_visibility_Weeks]=0),
FILTER(Query1, Query1[0 visibility exclusions]="No"),
FILTER(ALL(Query1),Query1[1MUSD Flag]=1 && Query1[PipelineExclusionIndicator]="N")
))

Test No Visibility% = DIVIDE(Query1[No Visibility #Deals],Query1[Total Deals],0)
test_Wow3 = Query1[Test No Visibility%]-[Test WOW3]
WOWTrend3 = IF([test_Wow3]<0,"▼",IF([test_Wow3]>0,"▲","--"))

Median Weeks of Visibility = FORMAT(MEDIANX(
SUMMARIZE(filter(Query1,Query1[Actual _Wk_Cond2]="Yes"
),Query1[Actual _Wk_Cond2],Query1[subregion],Query1[actual_visibility_Weeks],Query1[OpportunityId],"Viswks",
COUNT(Query1[OpportunityId])),
[actual_visibility_Weeks]),0)

test_Wow = Query1[%6week_1&2]-[Test WOW]

Test WOW1 =
VAR prv_wk =DATEADD(Query1[reportdate],-7,DAY)
Return

CALCULATE([Median Weeks of Visibility], FILTER(ALL(Query1),Query1[reportdate] in prv_wk &&
Query1[subregion]=SELECTEDVALUE(Query1[subregion])),
FILTER(ALL(Query1),Query1[1MUSD Flag]=1 && Query1[PipelineExclusionIndicator]="N"))

Total Deals = SUMX(VALUES(Query1[Temp_subregion]), Query1[%6week_1&2])

Some best practices used:

• Remove unused tables or columns, where possible. 
• Avoid distinct counts on fields with high cardinality – that is, millions of distinct values.  
• Take steps to avoid fields with unnecessary precision and high cardinality. For example, you could split highly unique datetime values into separate columns – for example, month, year, date, and so on. Or, where possible, use rounding on high-precision fields to lower cardinality – (for example, 13.29889 -> 13.3).
• Use integers instead of strings, where possible.
• Be wary of DAX functions, which need to test every row in a table – for example, RANKX – in the worst case, these functions can exponentially increase run-time and memory requirements given linear increases in table size.
• When connecting to data sources via DirectQuery, consider indexing columns that are commonly filtered or sliced again. Indexing greatly improves report responsiveness.  

Sill facing performance issue.

Thanks you

Regards,

Hari 

[v-lionel-msft]

Hi @harirao ,

Too many complicated measures cause DAX queries to spend too much time. But optimizing DAX performance is a complicated matter, maybe you can refer to these documents.

Performance Tuning DAX - Part 1 

Improve Power BI Performance by Optimizing DAX 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tex628]

I would start off by identifying if there is a single measure that is causing the long duration. If you remove measures while also monitoring performance you should be able to identify where the problem lies. 

Br,
J

[amitchandak]

@harirao , is it a direct query?

In between, see if query reduction can help

https://guyinacube.com/2019/01/30/power-bi-query-reduction-when-using-directquery/

https://radacad.com/directquery-connection-in-power-bi-how-does-it-work-limitations-and-advantages

[harirao]

i @amitchandak,

Thanks for your response, its Import Query used for this Dashboard.

Regards,
Hari