topic Re: SUM values from table A for groups in table B in DAX Commands and Tips

SUM values from table A for groups in table B

WOLFIE — Wed, 16 Dec 2020 16:56:12 GMT

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

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

False

True

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

Re: SUM values from table A for groups in table B

edhans — Wed, 16 Dec 2020 19:33:39 GMT

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.

Re: SUM values from table A for groups in table B

daxer-almighty — Wed, 16 Dec 2020 19:22:37 GMT

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

Re: SUM values from table A for groups in table B

WOLFIE — Wed, 16 Dec 2020 20:18:59 GMT

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.

Re: SUM values from table A for groups in table B

WOLFIE — Wed, 16 Dec 2020 20:24:05 GMT

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.

Re: SUM values from table A for groups in table B

daxer-almighty — Wed, 16 Dec 2020 20:27:53 GMT

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

Re: SUM values from table A for groups in table B

edhans — Wed, 16 Dec 2020 21:28:25 GMT

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