DAX: Selective sum

[PhotoBiker]

Hi community,

I'm trying to understand how to use DAX calculations on a flattened table, a simple "sum" doesn't work because the flattened table has too many rows.

Scenario: There's an event that lasts 2 hours. During this event, 2 workers simultanously provide service to 3 clients. Clients may be present during the entire event duration, or less. If I put this information in a flat table, it looks like this:

There are six rows because there are 2 workers x 3 clients. Client C1 attended 2 hours, C2 1 hour, and C3 0.5 hours, so "customer time" is 3.5 hours. The two workers each were available 2 hours, so in total that's 4 hours. Obviously, adding the columns would return wrong results.

I'm looking for a DAX statement that sums up time per worker and per client, but only once per event. The example above has only one event, but there would be many more, e. g. E2, where only 2 customers and 1 worker attended. So my goal is to consider values within the same group only once (I marked "same groups" by colour).

I'd really appreciate some help here, this would make my data model much easier (currently I can only achieve that by separating it into related tables).

Thanks in advance.

[v-chuncz-msft · Accepted solution]

@PhotoBiker,

It seems that you've resolved this problem here.

[PhotoBiker]

Hi @v-chuncz-msft,

Thank you for pointing that out - and sorry for the double posting (or rather not updating the post here), my bad!

Indeed, I received feedback in the other forum that offered me options to solve my issue.

Thanks for looking into it!