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 all.
Is there any other way to write this measure please? The measure works but performance is very poor.
TABLE 1:
Count of employees | Age | Gender | Group 1 | Group 2 | Group 3 | Group 4 | Group 5 | Group 6 | Group 7 | Group 8 | Group 9 | Group 10 |
1 | 20 | F | True | False | False | False | False | True | False | False | False | False |
1 | 20 | F | True | True | True | True | False | True | True | True | True | False |
2 | 22 | M | True | True | False | False | False | True | True | False | False | False |
5 | 36 | M | True | False | True | False | False | True | False | True | False | False |
6 | 57 | F | False | True | True | False | False | False | True | True | False | False |
4 | 54 | M | True | False | True | False | False | True | False | True | False | False |
7 | 52 | M | True | False | True | False | False | True | False | True | False | False |
8 | 32 | F | False | False | False | False | False | False | False | False | False | False |
9 | 25 | F | True | True | True | False | False | True | True | True | False | False |
4 | 32 | M | True | True | False | False | False | True | True | False | False | False |
8 | 25 | M | True | False | True | False | False | True | False | True | False | False |
1 | 54 | F | False | True | True | False | False | False | True | True | False | False |
1 | 33 | M | False | True | True | False | False | False | True | True | False | False |
1 | 21 | F | True | True | False | False | False | True | True | False | False | False |
TABLE 2:
Groups |
Group 1 |
Group 2 |
Group 3 |
Group 4 |
Group 5 |
Group 6 |
Group 7 |
Group 8 |
Group 9 |
Group 10 |
MEASURE :
@edhans @Thank you very much for your reply. The problem is that I cannot change the data model. Table with true-false has 30 million of rows after aggregation. The data is loading in very fast but I need to change the measure to make it faster. There is no relationship between these 2 tables.
@WOLFIE - Not sure I understand why you cannot change the table before it comes in. If someone is doing that for you upstream, talk to them and have that table unpivoted after the aggregation. My measure would work in milliseconds on 30M rows. DAX is designed for data to be in one column like I've done. I mean, literally designed as it is based on the SQL Server Analysis Services Tabular model. You are using a flat denormalized model, and that will get slower and slower over time. And your DAX is way WAY too complex because the model is badly designed as is.
You can read more here, and SQLBI has a book and course on modeling.
What is normalizing data? See the Normalizing an example table section of this paper
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingDo what @edhans says. Fact tables in Power BI should be long but narrow. Dimensions should be wide but short. Stick to this religiously.
Thanks @daxer-almighty I understand why and if I didn't work with that big data I would definitely stick to it. My initial model had 3 tables and each around 70 million of rows ", therefore I had to aggregate the data because it was struggling to refresh it and impossible to load all of that data in. Only this one measure is slowing it down.
If I understand your requirement @WOLFIE this measure works, and will be super fast.
Employee Count =
COALESCE(
SUMX(
FILTER(
'Employees Revised',
'Employees Revised'[Value] = TRUE()
),
'Employees Revised'[Count of employees]
),
0
)
However, you have to remodel your data. Your data isn't normalized. I normalized it. I unpivoted the Group* columns in your employee table so it look like this:
Then modeled it like this:
and it returns this:
You can see my full PBIX here. Go to Transform Data and look at the Unpivot operation in Power Query to see how I did the table modification. It is the Employee Revised table.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
53 | |
46 | |
15 | |
12 |