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.
So we have a workflow for engineers where they document their time against an opportunity, that once sold gets turned into a project, that they may then again document their time against. I'm trying to make a simple sold hours vs actual (opportunity hours + project hours) chart.
Our opportunity table is:
OppNo, ProjectNo
Our timebill table is:
UserId, Opportunity#, Project#, Time billed
The problem I feel I am running into is we have data in this table that looks like this:
james, (blank) , 12345, 3
Jim, 42453, (blank) 5
I tried to do a measure of calculate(sum(timebilled),project#=blank)) to only summerize the ones with opportunity listed but it isn't working. What is the correct forumula for this?
Hi,
You only want to sum up the hours where there is a blank in the Opportunity# column. Try this
=CALCULATE(SUM(Data[Time billed]),FILTER(Data,ISBLANK(Data[Opportunity#])))
Does this work? If not, then share the link from where i can download your PBI file.
Personally I try to avoid blanks/nulls and replace them with a dummy value.
I would also add a conditional column in M(Query Editor) or computed column to Set the Type e.g. Opportunity/Project
You could also then have 2 conditional colums/computed column or measures for Opp Time Billed and Project Time Billed.
They could be aggregated without a filter.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
82 | |
65 | |
62 | |
55 |
User | Count |
---|---|
171 | |
111 | |
110 | |
72 | |
71 |