Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Good morning!,
I'm working on a specific requirement for a report where I show job orders that do not have coverage (no client submissions).
I have a FACT Table that houses every record for a job order and I created a calculated column based on a measure to show if a client submission has been submitted, the column either shows a 0 or 1 based on if that job order record for the candidate has a client submission.
I'm trying to get the distinct job order that has no client submissions at all; however, I'm at an impasse on what I need to do.
I'd appreciate your help.
Here is my current expression:
Reqs W/O Coverage =
CALCULATE(
DISTINCTCOUNT(HI_JobOrder_BI_Fact[jobOrderID]),
FILTER(HI_JobOrder_BI_Fact,HI_JobOrder_BI_Fact[Client Submission] = 0
))
Here's what a record for a job order looks like:
As you can see there are candidates with no client submission, but 3 candidates who do have a client submission.
My calculation would need to exclude this type of occurrence.
I'm open to any solutions you might have in mind 🙂
We could not see your screenshot, please re-upload it again.
and for your case, if you could try this formula:
Reqs W/O Coverage =
CALCULATE (
DISTINCTCOUNT ( HI_JobOrder_BI_Fact[jobOrderID] ),
HI_JobOrder_BI_Fact[Client Submission] = 0
)
if not your case, please share your sample pbix file or sample data and your expected output.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Regards,
Lin
Sorry about the image not uploading properly, I had to remove it after the post itself was inaccessible on my end on Saturday.
Here's the previously blotted out image
I was able to come up with a calculation that provided me with what I was looking to achieve:
Req W/O Coverage =
SUMX( SUMMARIZE(HI_JobOrder_BI_Fact,HI_JobOrder_BI_Fact[ownerID],HI_JobOrder_BI_Fact[jobOrderID],HI_JobOrder_BI_Fact[Client Submission]),
CALCULATE(SUM(HI_JobOrder_BI_Fact[Client Submission]))
)
This allowed me to see if a job order had 0 submissions against all the candidate records within the fact table.
It works perfectly in a table visual; however, when I place the measure into a card to just display the number it provides isn't accurate.
If my DAX above is a part of the problem, I'm more than happy to adjust.
I can get a sample file together later in the day.
Your formula seems right. Can you share sample data and sample output?
User | Count |
---|---|
117 | |
75 | |
62 | |
50 | |
44 |
User | Count |
---|---|
174 | |
125 | |
60 | |
60 | |
57 |