Performance Issue with measures

[Anonymous]

Hi ,

I am having performace issue using these measures. any help in Optimizing this is greatly appreciated!

I am using these measures to satisfy certain criteria

/* Measure to satisfy the If condition below */

Measure 1 = 
Var _Length =selectedvalue('Sheet1 (2)'[Length])
return
If(_Length>5,1,2)

===================================
/* measure to count the occurrence of 1 in the above measure*/

Measure 2 = 
countrows(filter('Sheet1 (2)','Sheet1 (2)'[Measure 1]=1))

==================================
/* Measure to get the percent of occurrence of 1 )

Measure 3 = 
Countrows(Filter('Sheet1 (2)','Sheet1 (2)'[Measure 2]=1))/Countrows(FILTER('Sheet1 (2)','Sheet1 (2)'
                                                            [Measure 2](All('Sheet1 (2)'))))

Or 
Measure 3 = [Measure 2]/Countrows(FILTER('Sheet1 (2)','Sheet1 (2)'
                             [Measure 2](All('Sheet1 (2)'))))

I am using these similar formula for all the columns in a table. ( having 10 columns)

I also tried using the Measure 3 in different ways but performace is still the same. Looks like the  Filter function is causing the report to slow down since I am using it in 10 diffrent formula for each of my columns.

Any other way which can help increase the performace issue ??

Thanks

Tejaswi

[TomMartens]

Hey,

I'm sure I do not really understand what your requirement is, but nevertheless I created something 🙂

My table looks like this

I also created an unrelated table that I use to provide items for a slicer using this DAX statement:

Slicer Values = 
GENERATESERIES(1 , MAX('Table1'[Length]) , 1) 

Then I created this measure that counts the rows in my table with a length greater than the selection from the slicer:

no of rows (length) = 
var _Length =selectedvalue('Slicer Values'[Value])
return
SUMX(
    'Table1'
    , IF('Table1'[Length] > _Length , 1 , BLANK())
)

and this measure to calculate the percentage:

no of rows (length) percentage = 
DIVIDE([no of rows (length)] , COUNTROWS('Table1'))

All the above allows me to create a report that looks like this:

Maybe this will provide some ideas to solve your requirement, if not you may consider to create a Power BI file that contains some sample data, upload the file (also the excel file if you use excel to create the sample data) to onedrive or dropbox and share the link.

Regards,

Tom

[Anonymous]

HI @TomMartens ,

Thanks for your reply!

My table is somewhat like this:

DOC ID	Group	Type	Weight
1442236	SERVIC	ABCD	534.77
1444364	SERVIC	ABCD	0.55
1444640	SERVIC	ABCD	0.204
1445079	SERVIC	ABCD	1.633
1449308	SERVIC	ABCD	1
1451398	SERVIC	ABCD	4
1453189	KITASY	ABCD	1
1453193	ASY	ABCD	1
1453194	ASY	ABCD	1
1453203	ASY	ABCD	1
1453204	ASY	ABCD	1

Now I add my measures to this table : ( I have this measure based on some conditions if it meets the condition for Group column then it return 1 in measure. SImilarly if it meets the condition for Type column it will return 1 in measure column next to it and similar for the weight field.)

DOC ID	Group	Measure 1 for Group	Type	Similar Measure 1 for Type	Weight	Similar Measure 1 for Weight
1442236	SERVIC	1	ABCD	1	534.77	1
1444364	SERVIC	1	ABCD	1	0.55	1
1444640	SERVIC	1	ABCD	1	0.204	1
1445079	SERVIC	1	ABCD	2	1.633	2
1449308	SERVIC	1	ABCD	2	1	2
1451398	SERVIC	1	ABCD	2	4	2
1453189	ASY	2	ABCD	2	1	2
1453193	ASY	2	ABCD	2	1	2
1453194	ASY	2	ABCD	2	1	1
1453203	ASY	2	ABCD	2	1	1
1453204	ASY	2	ABCD	1	1	2

Now I want to  show the count of occurence of 1s in my each column ( as shown above_)

(for this I am using  Measure 2 in the original post)

For Example Measure:

Measure 1 for Group	Similar Measure 1 for Type	Similar Measure 1 for Weight
occurrence of 1 = 6	4	5

and then I used this above count in my Measure 3 to get the percentage.

I hope I am making this clear.

Thanks,

Tejaswi

[MFelix]

Hi @Anonymous ,

Check the link below with some explanations on how to improve and use the filters.

https://www.sqlbi.com/articles/optimizing-dax-expressions-involving-multiple-measures/

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

https://www.youtube.com/watch?v=B-h3Pohtn1Y

Regards,

MFelix