Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
Hi Pragati,
Here is sample data for the raw data. The data consists of:
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 = ""
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
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).
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
38 | |
26 | |
20 | |
15 | |
8 |
User | Count |
---|---|
69 | |
47 | |
46 | |
20 | |
16 |