Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello-
I am trying to create a visual that shows both TotalEffort (z_ISG_ProjectProgressQuery[EffortatBillingrate]) and DisciplineBudget by discipline (Budget measure calculated by the following dax formula using an inactive relationship:
DisciplineBudget = calculate([SV_MarketSectors_DisciplineBudget],userelationship[‘Job Titles’ [Discipline], SV_MarketSectors[SectorTypename}))
The visual on the right is pulling from in the Discipline name and Budget using the 'SV_MarketSectors' table and correctly shows budgets assigned to each discipline.
However, in the visual on the left, the Discipline Budget total pulls in correctly at the bottom, but the individual discipline budget amounts only pulls in when that discipline has corresponding Effort. If that disicpline has no effort, it does not pull in a budget amount (effort pulls in from the Z_ISG_ProjectProgressQuery table). I would like to see both the budget amount and effort by discipline, regardless of whether either of these items for that specific discipline is $0.
I need to be able to filter my visuals by individual project number (based on ProjectNum within the Z_ISG_ProjectProgressQuery table), so my active table relationships are set up to start at the JobTitles[Discipline] table to the Z_ISG_ProjectProgressQuery table by job code to be able to group the effort by corresponding discipline, and then the Z_ISGProjectProgressQuery has an active relationship with the SV_MArketSectors table connected through the ProjectID to obtain the appropriate discipline percentages for each individual project.
Any ideas on how I can acheive these? I have tried the "show items with no data" option for the visual and it still does not show the budget amount correctly if the effort for that discipline is $0.
@ldrysdale13 , There are two many to Many Bi-directional relations. You need to correct the data model
refer :https://www.sqlbi.com/blog/marco/2017/10/02/why-data-modeling-is-important-in-powerbi/
https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/
I think it's extremely difficult to solve this with the current information.
I would say that the filter path doesn't do what you want because of the multiple many-to-many relationships and bi-directional relationships.
The model looks very relational and maybe a rethink to get a star schema would be a good starting point.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |