How do I count the number of rows in a table with the same job number?

[croberts21]

Power BI version July 2024 on Windows Server 2019. We get data from a Data Lake (MS Azure Synapse Analytics SQL).

I have a table called "jobdetail". On this table are 2 fields: Jobid and jobdetailid. A jobid can have 1 or more jobdetailids listed for it. In a table viz I want to list each jobid once and list the number of jobdetailids for that jobid.

Example:

Jobid Count of Jobdetailid
1000  1
1001 1
1002  3
1003   4
1004 2

How do I do this? I thought I could add a column to the query with a dax formula but COUNT() counts all records in the query and returns the same number for each jobid. The number is about 20,000.

Thank you! You've been very helpful over the years.

EDIT: I will have to swing back to this in a week or so. Please be patient. Another project came up. 

[Kedar_Pande · Accepted solution]

Create a Measure for Counting jobdetailid:

Jobdetailid_Count = COUNT(jobdetail[jobdetailid])

Drag Jobid from the jobdetail table into the table visual.
Then, drag the Jobdetailid_Count measure you created into the visual.

If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[_AAndrade · Accepted solution]

Hi @croberts21,

I did a quick example with this data:

My DAX measure is this:

Count Job ID = COUNT('Table'[Jobdetailid])

 And the final output was this:

I hpoe this could help you.

[danextian · Accepted solution]

hI @croberts21 

If you simply want to count the number of jobdetailsid for each jobid and each row represents a single jobdetailsid, you can either COUNTROWS ( 'table' ) or COUNT ( 'table'[job id]) or any column from that same table.

[_AAndrade · Accepted solution]

Hi @croberts21,

I did a quick example with this data:

My DAX measure is this:

Count Job ID = COUNT('Table'[Jobdetailid])

 And the final output was this:

I hpoe this could help you.

[Kedar_Pande · Accepted solution]

Create a Measure for Counting jobdetailid:

Jobdetailid_Count = COUNT(jobdetail[jobdetailid])

Drag Jobid from the jobdetail table into the table visual.
Then, drag the Jobdetailid_Count measure you created into the visual.

If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn