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 everyone !
Relatively new to PowerBI (IT consultant over here) and working for a client on Project Portfolio at the moment.
The main issue so far is with time: projects always have a start date & end date, and the main filter in the expected dashboard will be with a Time Slicer. Today I have a working "quick and dirty" way to filter this by having PowerBI create a flat table. For example a project from January to March would be:
"Project A - January - Workload X - Benefit Y"
"Project A - February - Workload X - Benefit Y"
"Project A - March - Workload X - Benefit Y"
Apart from the month, all other values are the same (yes, workload is an average).
This makes the slicer work fine, but it's (as expected) horrible for KPIs.
Example: "how much workload do I need to cover all the planned project from January to March?" Expected user behavior would be to look at the slicer, the dates, and then look at the big number "Required Workload". Problem is, with the flat list shown above, this will multiply the average workload 3 times: because it's 3 months project and therefore 3 lines.
Making a custom measure in PowerBI just for this makes me think that the model is not the right one.
Any thoughts?
I think it will be easier if you handle this in measure. So datamodel remains as it originally is, with Project ID, Workload, Start Date, End Date, etc.
When you want to compute a measure, you could try (assuming you have a Date dimension):
Measure = VAR FirstDay = MIN ( Date[Date] ) VAR LastDay = MAX ( Date[Date] ) RETURN CALCULATE ( [Workload Measure], 'Fact'[Start Date] <= FirstDay, 'Fact'[End Date] >= LastDay )
In this approach, you don't need to setup a relationship between the Date dimension and the Fact table.
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 |
---|---|
110 | |
98 | |
79 | |
64 | |
56 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |