Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi people,
So I basically have the Beginning and Ending Date for my projects. And I need, as a part of a bigger challenge, to intersect the number of days when the project was active with the current dates filter provided by the user. My measure works on the level of the project, but completely fails in the totals (which should be the sum of every project active days). I believe in the totals the measure considering an intersection of 365 days for every project or something like that ...
For example: Project 1: 5 days active/2020 and 5 days active 2021 //// Project 2 10 days active/2020, 10 days active 2021.
The result shouls be 15 days total /2020 and 15 days 2021
PS: the measure refers to 2 tables (Calendar and Project) that are not related in the model
Solved! Go to Solution.
Hi @campelliann,
Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi,
Totals are often an issue, since they use logic that isn't always intuitive. I recommend cheking this article by SQLBI to understand why the value you got doesn't make sense: https://www.sqlbi.com/articles/summing-values-for-the-total/
One easy solution to solve issues with totals is to use IF with SELECTEDVALUE to change the calculation logic for the total row. Example:
Data:
Dax:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
Proud to be a Super User!
Here is my latest attempt with summarize.
I remind that the goal here is to intersect the days in which each project was active and the current dates filter context.
Example: Project 1 occured from 1 january to 15 february. The output is 31 days worked in January and 15 in February.
Than I want to sum all the days worked for each month...
It seems that in the totals, it ignores the dates from the projects. When the project is as filter context it works. So I tried to build a new global summarized virtual table, still it does not work, why?
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |