Forum Discussion

sirlanceohlott's avatar
sirlanceohlott
Icon for Advocate III rankAdvocate III
6 years ago

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:

 

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 🙂

Need Help
Tips and Tricks

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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

     

     

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    hi  sirlanceohlott 

    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

    • sirlanceohlott's avatar
      sirlanceohlott
      Icon for Advocate III rankAdvocate III
      6 years ago

      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

       

      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.