Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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
 
 

 

2 ACCEPTED SOLUTIONS
d_gosbell
Super User
Super User

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

View solution in original post

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

View solution in original post

4 REPLIES 4
d_gosbell
Super User
Super User

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

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

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

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

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.