Visual using measures runs slow

[Anonymous]

Hi,

I have a table visual where each columns have some data and I have created measures to give me the output for each of these columns as 1 and 0 based on each columns criteria.

so for example I have 10 columns , I created 10 measures , each measures has a criteria formula for each of these columns which returns 1's and 0'.  below is the 1 measure. 

Highlight W =

Var WID=SELECTEDVALUE('Table'[Width])

Var MT = Selectedvalue('Table'[Material type])

Var _Width=

/*blank or <0.01*/

if(MT<>"ZOG" && (WID<0.01 || WID=BLANK())

,1,0)

Return

_Width

i created other 10 measures to count the occurence of 1's.  something like this

Value of Highlight W =

Var _W=Countrows(filter('Table','Table'[Highlight W]=1))

return _W

I now put all  10 measures which gives me the occurence of 1's  in a funnel visual. ( i have created to table to make this visual work with measures)

My measures are working and showing result as expected.. However the performace is very slow .

 may be I am having a filter function in each of these measures.

Any help in tweaking this measure which help improve the performace?

Thanks,

Tejaswi

[d_gosbell · Accepted solution]

So the problem with measures that use patterns like IF( <condition>, 1, 0 ) is that they effectively generate a lot of data that does not exist in your data source. If you have sales data and you table a table with Customer, Product and Date you are forcing the engine to calculate a value for every product for every customer on every day.

Since you are only interested in the 1's changing this to IF( <condition>, 1, BLANK() ) should dramatically increase your performance as the tabular engine is very good at skipping blanks.

But possibly an even faster approach is to just do a countrows of your filter condition which will avoid the IF logic entirely

Value of Highlight W =
countrows(
   Filter('Table', 'Table'[Material Type]<>"ZOG" && ('Table'[Width] < 0.01 || ISBLANK( 'Table'[Width] )

)

[d_gosbell · Accepted solution]

So the problem with measures that use patterns like IF( <condition>, 1, 0 ) is that they effectively generate a lot of data that does not exist in your data source. If you have sales data and you table a table with Customer, Product and Date you are forcing the engine to calculate a value for every product for every customer on every day.

Since you are only interested in the 1's changing this to IF( <condition>, 1, BLANK() ) should dramatically increase your performance as the tabular engine is very good at skipping blanks.

But possibly an even faster approach is to just do a countrows of your filter condition which will avoid the IF logic entirely

Value of Highlight W =
countrows(
   Filter('Table', 'Table'[Material Type]<>"ZOG" && ('Table'[Width] < 0.01 || ISBLANK( 'Table'[Width] )

)

[Anonymous]

Thanks @d_gosbell ,

It now  works a little faster compare to previous.

However, I also have a measure which calculates the % of occurence of 1's

I have used this measure base on my previous measure.

% Width Discrepancy = [Value of Highlight W]/Countrows(FILTER(zcvrMaterial,zcvrMaterial[Highlight W](All(zcvrMaterial))))

after adjusting the formula as per your suggestion i am using now this below measure which errors out.

% of width =
   Filter('Table', 'Table'[Material Type]<>"ZOG" && ('Table'[Width] < 0.01 || ISBLANK( 'Table'[Width] )/Countrows(FILTER('Table','Table'[Hightlight of Width] (All(zcvrMaterial))))

now i get this error:

the expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Any help is really apprecaited!

Thanks,

Tejaswi

[d_gosbell · Accepted solution]

So you cannot divide the FILTER() function (which returns a table with multiple rows and columns) by a count rows. You should not need to change the [% of width] measure at all - the old measure that references [Value of Highlight W] should still work.

Or you could simplify it and reduce it down to the following (note I recommend getting in the habit of using the DIVIDE() function instead of the numeric / operator as it safely handles divide by 0 or divide by blank) 

% of width = DIVIDE( [Value of Highlight W] , Countrows( All(zcvrMaterial) ) )

[Anonymous]

Thanks @d_gosbell ,

This has now solved my problem.

I did get the infinite output as my value was getting divide by blank.

I have now used the divide function.

Thanks for all your help!

-Tejaswi