Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
frank_88
Frequent Visitor

Wrong measure total and bridge table

 

Hi guys,

 

I am a newbie to Power BI and I believe this is a simple issue but I am running circles now and need some help. 

 

I have a fact table (F_ACTIVITIES) that lists tasks made everyday by employees. Tasks can be done in solo or duo. So in fact table there is a column ID_EMPLOYEE_RESOURCE that is linked to the bridge table setup.

 

F_ACTIVITIES is linked to D_EMPLOYEE_RESOURCE by ID_EMPLOYEE_RESOURCE

D_EMPLOYEE_RESOURCE is linked to D_EMPLOYEE_BRIDGE

D_EMP^LOYEE_BRIDGE is linked to D_EMPLOYEE

 

I have a simple table that lists the number of tasks for each employee, number of working days, and a tasks/day ratio. This table has a lot of classic slicers (month, day, team, type of task, etc).

 

The employee name comes from the D_EMPLOYEE table and all the other measures are from the F_ACTIVITIES table. 

 

On a row by row basis, everything is ok. I get the correct nb of tasks, and the correct nb of working days. However , the totals are wrong because some tasks are done in duos and they count as 1 record in the fact table. So to give you a simplified exemple I have this situation : 

 

Name                       NB days     NB Tasks

Employee A solo           1                 3

Employee B duo            1                 4

Employee C duo            1                 4

Employee D solo            1                 2

----------------------------------------------

Total                               3                 9

 

I can't figure out how to get the proper totals (4 and 12). There must a way, no ??

 

Any help would be very appreciated !!!

 

Thanks in advance

 

Francois

 

5 REPLIES 5
v-eachen-msft
Community Support
Community Support

Hi @frank_88 ,

 

Could you please share your sample data and excepted result to me if you don't have any confidential Information. Please upload your files to One Drive and share the link here.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi guys, 

 

thanks for the replies. I created a similar sample file. The link to the file is here:

https://usherbrooke-my.sharepoint.com/:u:/g/personal/savf2106_usherbrooke_ca/EeX-59l5rAtDo17AkUGDBSU...

 

I tried something similar to the idea in this blog: 

https://powerpivotpro.com/2012/11/a-mystifying-and-awesome-solution-for-many-2-many/

 

Regards,

 

Francois

Hi @frank_88 ,

You almost had it, but if you see my picture below, and think of filters flowing downhill, you will see that you selected the nom from the wrong table. With bridge tables available, you want to choose them. 2nd picture shows it working


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

agent2.PNG

 

 

 

agent.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi Nathaniel, 

 

Tanks for your quick post. For a brief moment I had hope 🙂

 

If might not have been clear on the original sample data but yes, I want the data from the d_AGENTS table. 

 

The Agents are Pouliot, Chiasson, Legrand and Columbo. They can do a task in solo or in duo. The D_AGENTS_RESOURCE is a solo or duo record and the D_AGENT_BRIDGE table is the composition of that duo_solo. So you can see that id_agent_resource 2 is composed of agent 1 and agent 2. 

 

So if we read the fact table, we see that Pouliot did 4 tasks (2 solos, 2 duos). Columbo did only 1 task in solo. So I really want to have the Agent name (NOM) and not the D_AGENTS_RESOURCE NOMS column, which is the description of the solo or duo agents doing the task.

 

 

Nathaniel_C
Super User
Super User

Hi @frank_88 ,

 

Working on something right now, but the logic seems to be the issue. You may need to differentiate between a solo task and a duo task, because isn't the second twice the work?

 

Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.