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

 

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

4 Replies

  • d_gosbell's avatar
    d_gosbell
    Super User
    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] )
    )
     
    • 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) ) )