topic Re: Need help with DAX to create a card identifying Count of employeeID with no sales in DAX Commands and Tips

Need help with DAX to create a card identifying Count of employeeID with no sales

hong88 — Mon, 02 Sep 2024 13:45:20 GMT

Hi there,

I am new to this community, but I have read through a number of solutions and it was of great help.

I currently have a problem figuring out how to create a card to identify the count of employees with 0 sales for both years and have worked in the company for more than 1 year.

I created a little mock table with the relevant details.

I really hope someone can help me, I am truly at a loss at the moment.

Re: Need help with DAX to create a card identifying Count of employeeID with no sales

PabloVallejo12 — Mon, 02 Sep 2024 14:00:07 GMT

Did you try to use the summarize function, with a filter in sales amount = 0 ?

Re: Need help with DAX to create a card identifying Count of employeeID with no sales

hong88 — Mon, 02 Sep 2024 14:29:50 GMT

I tried, but im a bit confused because the IDs appear twice and have to be filtered together, meaning if ID 2233 has 0 sales in 2022, but has 9915 in sales in 2023, it should not appear as an ID without sales for both years.

The only ones that should be counted are IDs with both years = 0 and tenure > 1.

I tried using CALCULATE(countrows(table),
filter(values(table[SalesAmount]),
Calculate(SUM(table[SalesAmount])=0)

Basically what I got back are just the rows with 0, im not sure how to futher filter based on same employeeID for 2022 and 2023 = 0 and tenure >1.

Re: Need help with DAX to create a card identifying Count of employeeID with no sales

PabloVallejo12 — Mon, 02 Sep 2024 14:28:08 GMT

Maybe you can create a measure for each sales year, and use it for filter

Re: Need help with DAX to create a card identifying Count of employeeID with no sales

hong88 — Mon, 02 Sep 2024 17:09:04 GMT

edit: I think ive partially solved it, still cant figure out how to group it by ID and filter it if both years = 0. But I've decided to filter twice by year and by tenure > 0.

Thanks for your help, ur ideas helped me think of alternative ways to analyse. But if anyone still knows the DAX for the original problem, I would be really interested to find out too 😊

Re: Need help with DAX to create a card identifying Count of employeeID with no sales

Anonymous — Tue, 03 Sep 2024 01:35:43 GMT

Hi hong88

Please try this:

Here is the sample data:

'Table':

Then add a measure:

MEASURE = VAR _vtable = FILTER ( ADDCOLUMNS ( 'Table', "_Sumsales", CALCULATE ( SUM ( 'Table'[Sales Amount] ), 'Table'[EmployeeID] = EARLIER ( 'Table'[EmployeeID] ) ), "_MaxTenure", CALCULATE ( MAX ( 'Table'[Tenure] ), 'Table'[EmployeeID] = EARLIER ( 'Table'[EmployeeID] ) ) ), [_MaxTenure] > 1 && [_Sumsales] = 0 ) RETURN COUNTROWS ( SUMMARIZE ( _vtable, 'Table'[EmployeeID] ) )

The variable [_vtable] returns:

So the result is as follow:

Best Regards

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

Re: Need help with DAX to create a card identifying Count of employeeID with no sales

Irwan — Tue, 03 Sep 2024 02:36:57 GMT

hello hong88

please check if this accomodate your need.

for count employee with zero sales in both year, create a new measure with following DAX.

Count =
var _Sales = 
SUMMARIZE(
    FILTER(
        SUMMARIZE(
            'Table',
            'Table'[EmployeeID],
            "Sum",
            SUM('Table'[Sales Amount])
        ),
        [Sum]=0
    ),
    'Table'[EmployeeID]
)
Return
COUNTROWS(_Sales)

if you want to do with SUMMARIZE, then just copy that _Sales DAX into new table.

Summarize = 
SUMMARIZE(
    FILTER(
        SUMMARIZE(
            'Table',
            'Table'[EmployeeID],
            "Sum",
            SUM('Table'[Sales Amount])
        ),
        [Sum]=0
    ),
    'Table'[EmployeeID]
)

if you need another filter for Tenure>0, then just add that line inside the FILTER.

Hope this will help.

Thank you.

Re: Need help with DAX to create a card identifying Count of employeeID with no sales

hong88 — Thu, 05 Sep 2024 14:45:06 GMT

Thanks everyone for your suggestions! couldnt have been able to do it without ur help. 😊