CALCULATE SUM for all UserIDs when UserID text is contained in the string value of another column

[cescovedo]

Hi, I'm trying to CALCULATE the SUM of meeting hours for each userID, by creating a new table from a reference table. However, I have a column for primary userID, and a separate column for secondary userIDs, which is a string of comma separated userIDs. I can do it easily for the primary userID column, but I'm unable for the secondary userIDs, probably because I need to search the value for text by iterating through each userID. I tried transforming the secondary userID column to expand into additional rows and separate the comma-separated string, but then that I would have to calculate the sum of distinct values for the primary userID column, and can't find a way to do that either.

'Meeting Hours' table

MeetingID	MeetingHours	PrimaryUserID	SecondaryUserID
1	1	10	11, 12
2	0.5	11	10,12,13,14,15
3	1	12	13
4	0.25	13	10, 12
5	0.5	14	10, 13
6	0.5	15	11,14

'Summary' table

PrimaryUserID	PrimaryMeetingHours	SecondaryMeetingHours
10	1	1.25
11	0.5	1.5
12	1	1.75
13	0.25	2
14	0.5	1
15	0.5	0.5

[OwenAuger · Accepted solution]

Hi @cescovedo 

Here is how I would suggest setting up the model (PBIX attached).

I have added some made-up attributes for illustrative purposes.

User (dimension)

Meeting (dimension)

MeetingHours (fact)

MeetingUser (fact/bridge)

MeetingUser is constructed by splitting SecondaryUserID by comma into rows, then appending Primary & Secondary users (see Power Query).

You can then create measures like:

Meeting Hours = 
SUM ( MeetingHours[MeetingHours] )

Primary Meeting Hours = 
CALCULATE (
    [Meeting Hours],
    KEEPFILTERS ( MeetingUser[UserType] = "Primary" )
)

Secondary Meeting Hours = 
CALCULATE (
    [Meeting Hours],
    KEEPFILTERS ( MeetingUser[UserType] = "Secondary" )
)

To construct a visual similar to your Summary table, you can either:

1. Create a Matrix with User[UserID] on rows, MeetingUser[UserType] on columns, and Meeting Hours on Values.

2. Create a Table/Matrix with User[UserID] on rows, and two measures Primary Meeting Hours and Secondary Meeting Hours.

Would something like this work for you?

[cescovedo]

Amazing, thank you! This makes a lot more sense to do it this way. I was able to build the model and tables the way you outlined, but I think I'm going wrong somewhere with the calculation measures, because the hours are adding up to "10" for every UserId. I did get an error that one of my MeetingHours columns was text instead of numeric, so I fixed that, but that didn't fix the issue with the visualization tables/matrices.

I don't see a way to attach my pbix file, how did you do that?

[OwenAuger]

Hi @cescovedo 

Thanks for the update!

Actually attaching files is a Super User-only feature on these forums.

You can share via Google Drive, OneDrive etc and post the link.

In the meantime, one thing to check is the relationship between MeetingUser and Meeting has Cross filter direction set to "Both".

regards

[cescovedo]

Hi again,

Was hoping you could help me with a related question. In my USER table, I have a new column for how many hours the given user can work. I wanted to create a measure for these, and then create another measure that subtracts the sum of primary+secondary hours from the available hours. It looks like the measure is iterating through every user and not matching on the EISPhysicianID.

This is partly because I don't totally understand what you're doing with the Meeting Hours/Primary/Secondary measures. It seems like you're using the SUM aggregation measure, and that is iterating through the MeetingUser table for each ID and EISPhysicianID.

So perhaps I need a table similar to MeetingUser that joins the Meeting ID, EISPhysicianID, and AvailableHours?

I assume I would also have to update the model, but its not letting me make the connection, presumably because I don't have the right columns between those 2 tables.

I suppose the main question is: Can you put Measures from different tables into the same matrix, or no?

https://drive.google.com/file/d/1iu9UuGfrC5Z97kcjijTjtLyGV3Xi3smD/view?usp=sharing

[OwenAuger · Accepted solution]

Hey @cescovedo 

Sure thing, I had another look at this and attached an updated PBIX 🙂

I adjusted a things. The key changes are:

• You should generally use User[EISPhusicianId] in visuals, not MeetingUser[EISPhusicianId]. I changed this in the table visual.
  The reason is that User is intended to be the User dimension that filters all other related tables. MeetingUser is a bridge table that ensures Users are related to both their Primary & Secondary meetings, but doesn't need to be visible when creating reports. I have hidden MeetingUser for now.
• Adjusted the Unscheduled Hours measure as follows:
  

  Unscheduled Hours = 
  SUMX (
      User,
      [Available Hours] - [MeetingHours]
  )

  SUMX iterates over User and adds Available Hours - MeetingHours for each.
  MeetingHours should always be equal to Primary Meeting Hours + Secondary Meeting Hours (as long as the same user isn't Primary & Secondary in the same meeting).
• In the visual below, note that Primary & Secondary Hours totals only count each meeting once. You could sum these per User by wrapping in SUMX ( User, ... ) if you wanted.

Hope that's some help!

Regards

[cescovedo]

Thank you! Yes that seems to have done it. I came across SUMX but thought it wasn't right for some reason. The primary and secondary hours are counting things up appropriately so somehow that seems to be working without SUMX. But I could change it for consistency and future proofing.  Really appreciate your help with this.

[cescovedo]

That did it! Thank you so much, can't tell you how helpful this was. This makes me want to go back and rethink other projects as well. I may be back here before long haha. Thanks,

[OwenAuger · Accepted solution]

Hi @cescovedo 

Here is how I would suggest setting up the model (PBIX attached).

I have added some made-up attributes for illustrative purposes.

User (dimension)

Meeting (dimension)

MeetingHours (fact)

MeetingUser (fact/bridge)

MeetingUser is constructed by splitting SecondaryUserID by comma into rows, then appending Primary & Secondary users (see Power Query).

You can then create measures like:

Meeting Hours = 
SUM ( MeetingHours[MeetingHours] )

Primary Meeting Hours = 
CALCULATE (
    [Meeting Hours],
    KEEPFILTERS ( MeetingUser[UserType] = "Primary" )
)

Secondary Meeting Hours = 
CALCULATE (
    [Meeting Hours],
    KEEPFILTERS ( MeetingUser[UserType] = "Secondary" )
)

To construct a visual similar to your Summary table, you can either:

1. Create a Matrix with User[UserID] on rows, MeetingUser[UserType] on columns, and Meeting Hours on Values.

2. Create a Table/Matrix with User[UserID] on rows, and two measures Primary Meeting Hours and Secondary Meeting Hours.

Would something like this work for you?