Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
sirlanceohlott
Advocate III
Advocate III

DISTINCT COUNT With Parameters - Job Submission

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:

Table Example_3.PNG

 

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 🙂

3 REPLIES 3
v-lili6-msft
Community Support
Community Support

hi  @sirlanceohlott 

We could not see your screenshot, please re-upload it again.

1.JPG

 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-lili6-msft & @amitchandak,

 

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

Table Example_3.PNG

 

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.

 

 

 

 

amitchandak
Super User
Super User

Your formula seems right. Can you share sample data and sample output?

 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.