Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All! I've just begun learning PBI and my background is mostly Excel. I know in my heart this formula/query might be simple but I just haven't been able to make it work. I'm trying to "label" each sum result according to either above or below 1 to know the offenders on time tracking in my team.
My ideal matrix visual would look like this, the whole page is filtered by current month so the "offense" formula would have to be dinamic? or integrate filters? (I've tried this with calculate-sumx-filter withour sucess)
Resource | Time | Offense |
Name 1 | 0.3 | Incomplete |
Name 2 | 1.5 | Overtime |
Name 3 | (blank) | Missing |
The matrix is composed of one list from sharepoint, there are many columns but this are the important ones. Resource ID has a relationship with the Header list that brings other info like management.
1. Detail
Project | Resource | Resource ID | Month | Time |
Project1 | Name 1 | 1 | 10/2/2019 | 0 |
Project2 | Name 1 | 1 | 10/1/2019 | 0.1 |
Project3 | Name 1 | 1 | 10/14/2019 | 0.5 |
Project1 | Name 2 | 2 | 10/5/2019 | 1 |
You can see a single person can be in many projects and for each month enter different time but we are really trying to label the offense based on every month's total.
This is some of what I've tried so far by creating a Measure I can add to the matrix... but it does not work or it labels everything "incomplete". Appreciate any help since my deadline is near
Var sumar = CALCULATE( SUM(FTEDetail[Actual]), FILTER(FTEDetail,FTEDetail[MonthFTE]=MONTH(TODAY())), FILTER(FTEDetail,SELECTEDVALUE(FTEDetail[ResourceID]) ) Var tag = IF(sumar>1,"Overtime",IF(sumar=1,"Complete",IF(sumar<1,"Incomplete","Missing"))) return tag
Solved! Go to Solution.
What you are trying to achieve shouldn't be too complicated.
I took the sample you provide and came up with 2 measures. You can paste the first measure in the second measure if you rather have it all together.
Measure 1:
Measure = CALCULATE ( SUM ( FTEDetail[Time] ); MONTH ( FTEDetail[Month] ) = MONTH ( TODAY () ) )
Measure 2:
Measure 2 =
SWITCH (
TRUE ();
[Measure] = 1; "Complete";
[Measure] > 1; "Overtime";
[Measure] < 1; "Incomplete";
"Missing"
)
If you have any questions don't hesitate to ask.
Kind regards
Joren Venema
Data & Analytics Consulant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.
What you are trying to achieve shouldn't be too complicated.
I took the sample you provide and came up with 2 measures. You can paste the first measure in the second measure if you rather have it all together.
Measure 1:
Measure = CALCULATE ( SUM ( FTEDetail[Time] ); MONTH ( FTEDetail[Month] ) = MONTH ( TODAY () ) )
Measure 2:
Measure 2 =
SWITCH (
TRUE ();
[Measure] = 1; "Complete";
[Measure] > 1; "Overtime";
[Measure] < 1; "Incomplete";
"Missing"
)
If you have any questions don't hesitate to ask.
Kind regards
Joren Venema
Data & Analytics Consulant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
88 | |
88 | |
70 | |
69 |
User | Count |
---|---|
227 | |
127 | |
118 | |
82 | |
80 |