SUM values from table A for groups in table B

[WOLFIE]

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 :

Employee Count By Group =

var List = VALUES('Groups'[Groups])

RETURN

SUMX(FILTER('Employees',

("Group 1" IN List && 'Employees'[Group 1]) ||

("Group 2" IN List && 'Employees'[Group 2]) ||

("Group 3" IN List && 'Employees'[Group 3]) ||

("Group 4" IN List && 'Employees'[Group 4]) ||

("Group 5" IN List && 'Employees'[Group 5]) ||

("Group 6" IN List && 'Employees'[Group 6]) ||

("Group 7" IN List && 'Employees'[Group 7]) ||

("Group 8" IN List && 'Employees'[Group 8]) ||

("Group 9" IN List && 'Employees'[Group 9]) ||

("Group 10" IN List && 'Employees'[Group 10])

),[Count of employees]) + 0

[daxer-almighty]

@WOLFIE, no amount of DAX can make a BAD MODEL fast.

[WOLFIE]

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

[edhans]

@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

[daxer-almighty]

Do what @edhans says. Fact tables in Power BI should be long but narrow. Dimensions should be wide but short. Stick to this religiously.

[WOLFIE]

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.

[edhans]

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.