Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Visual using measures runs slow

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 examp...
Need Help
  • d_gosbell's avatar
    d_gosbell
    6 years ago

    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's avatar
    d_gosbell
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
d_gosbell
Super User
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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