topic contains function is very slow in DAX Commands and Tips

contains function is very slow

Anonymous — Mon, 06 Apr 2020 11:41:19 GMT

Hi,

I have a measure that checks for the existence of certain rows with specific conditions and returns certain values if the condition is met as per the pseudo code below:

if a row with condition1 exists:

{ return value1}
else if a row with condition2 exists
{ return value2}
else if a row with condition3 exists
{ return value3}
else

{return blank}

I wrote the dax measure as follows:

IF(CONTAINS('TableX','TableX'[ColumnA],"Condition1"),"Value1",IF(CONTAINS('TableX','TableX'[ColumnA],"Condition2"),"Value2",IF(CONTAINS('TableX','TableX'[ColumnA],"Condition3"),"Value3",BLANK())))

The measure is extremely slow. Can someone please help me optimizing it?

Re: contains function is very slow

Greg_Deckler — Mon, 06 Apr 2020 12:08:24 GMT

Perhaps replace your CONTAINS with IN?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: contains function is very slow

Pragati11 — Mon, 06 Apr 2020 13:14:53 GMT

Hi Anonymous ,

Can you please post some screenshots on the data you are using in your pseudo code?

It is hard to judge what is the issue with your DAX expression with CONTAINS.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

Re: contains function is very slow

Anonymous — Mon, 06 Apr 2020 13:20:11 GMT

Thanks Greg. However, containsrow and in do not serve my purpose. They are both used as filters for the filter function as per this explanation https://docs.microsoft.com/en-us/dax/in-operator-containsrow-function

In my scenario, I want to check the existance of a row in a table. I need the equivalent of IF EXISTS(SELECT * FROM Table1 WHERE Column1 = Value1) in SQL. This is why I used the CONTAINS but unfortunately, it is extremely slow that the visual is not even loading.

Re: contains function is very slow

Anonymous — Mon, 06 Apr 2020 14:11:35 GMT

Hi Pragati,

Here is sample data for the raw data. The data consists of:

Dates
Employees
Activities to be performed by employees denoted by A1, A2, A3, etc...
Activity Status which indicates whether an activity was complete or not. The values are as follows:
- If the activity was completed, then the data shows "Yes"
- If the activity was not completed, then the data is "No"
- If no information was provided, we have "Unknown"
HoursSpent: when an activity is completed, this column indicates the number of hours it took the employee to complete this activity

RAW DATA
Date	Employee	Activity	Activity Status	HoursSpent
01/04/2020	E1	A1	Yes	3
01/04/2020	E1	A2	No
01/04/2020	E2	A1	Unknown
01/04/2020	E3	A1	No
02/04/2020	E1	A2	No
02/04/2020	E2	A1	Yes	6
02/04/2020	E3	A1	Unknown
02/04/2020	E3	A2	Yes	1
03/04/2020	E1	A1	Yes	1
03/04/2020	E1	A2	Unknown
03/04/2020	E2	A1	No
03/04/2020	E2	A2	Yes	3
03/04/2020	E2	A3	Yes	2
03/04/2020	E3	A1	Unknown

The above data needs to visualized as in a matrix visual as shown below:

REQUIRED RESULT
	01/04/2020	02/04/2020	03/04/2020
E1	3	No	1
E2		6	5
E3	No	1

The Employees are the rows while the dates are the columnn. The values are the result of the measure I am trying to create described below:

IF (Employee completed ANY activity on particular day -- here we do not care about the activity. all we care for is whether the employee completed any of the activities or not on that particular day)

Result = SUM(HoursSpent)

ELSE IF (Employee did not complete any activity on particular day -- here too all we care about is whether the employee did not complete any activity on that particular day)

Result = "No"

ELSE

Result = ""

Re: contains function is very slow

Pragati11 — Mon, 06 Apr 2020 15:12:49 GMT

Hi Anonymous ,

A question here. How can you create a calucation "Result" having 2 different data-types in it?

In your case SUM(Hours_Spent) is Numeric and Activity Status is String.

Your Result column can't have values with 2 different data-types.

Do you still want to do something like this or try a different way of representing your data?

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

Re: contains function is very slow

Anonymous — Mon, 06 Apr 2020 15:52:20 GMT

Hi Pragati11 ,

You can have 2 different data-types with measures but you can not do it with calculated columns. I have already done the logic and the measure works perfectly fine as indicated in my first post. The issue is that it is very slow. When I use it in a table it works perfectly fine but when I use it in a matrix visual, it takes forever to load (24minutes to be exact).

Re: contains function is very slow

Pragati11 — Mon, 06 Apr 2020 16:03:39 GMT

Hi Anonymous ,

I actually took your calculation as a caluclated column that's asked that 2 different data-type thing. 🙂

Yes for measure it works.

I will see if I can get a way to fix this.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati