topic Count of jobs when country and client is selected in DAX Commands and Tips

Count of jobs when country and client is selected

ODeb — Sat, 25 Jan 2025 06:26:41 GMT

Can anyone help me with dax for total count of jobs when company and client is selected in slicer . I have job table with job number column , company table with company name column and client table with client name .All the dimensions tables are related to job actual fact table by job key , company key and client key

Re: Count of jobs when country and client is selected

DataNinja777 — Sat, 25 Jan 2025 11:23:43 GMT

Hi ODeb ,

To calculate the total count of jobs when a company and client are selected in slicers, you can create a DAX measure that respects the filters applied by these slicers. Given your data model, where the Job Table is related to the Company Table and Client Table via their respective keys (Job Key, Company Key, and Client Key), the measure can be written to count the rows in the Job Table that remain after applying the slicer filters.

The DAX measure for this can be written as:

Total Jobs = COUNTROWS('Job Table')

This measure counts the number of rows in the Job Table. The slicers, placed on Company Name (from the Company Table) and Client Name (from the Client Table), automatically filter the data model. These filters propagate through the relationships to the Job Table, ensuring that only the relevant rows corresponding to the selected company and client are included in the count.

To implement this, ensure that your relationships between the tables are correctly defined in the model. Once the measure is created, you can add it to a card visual or any other visual in Power BI to display the total count of jobs based on the slicer selections.

Best regards,

Re: Count of jobs when country and client is selected

ODeb — Sat, 25 Jan 2025 17:41:36 GMT

Job , Company and client are the dimensions Job Actual table is the fact table and connected by respective keys to dimension table ..

Re: Count of jobs when country and client is selected

Anonymous — Mon, 27 Jan 2025 05:24:06 GMT

Thank you DataNinja777

Hi, ODeb

Based on your description, I've created this sample dataset below:

Use two dimension tables to build two slicers:

Establish the following measure:

Jobs Count = CALCULATE( COUNT('Job'[Job Number]), ALLSELECTED('Company'[Company Name]), ALLSELECTED('Client'[Client Name]) )

Here are the results:

I've provided the PBIX file used this time below.

Best Regards

Jianpeng Li

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