Count Column Instances of Number based on Multiple Cell Criteria

[Marks9172]

Customers are scanned multiple times a day and I need a way to count the number of occurences of a customer number on each day and for each product they one. I will then divide 1 by the number of occurrences based on matching the customer number, product and created at date fields to get an actionable field for calculating total visits.

Sample Table 

I am attaching an example table for reference and am trying to create the Admissions-Scanned column in my data table.  

I have tried multiple solutions but I am not getting the desired result. 

The excel formula I have used in the past is 

=IF(ISBLANK([@[Product Name]])=FALSE,1/COUNTIFS([Customer Number],[@[Customer Number]],[Product Name],[@[Product Name]],[Created At Date],[@[Created At Date]]),0)

[Bibiano_Geraldo · Accepted solution]

Hi @Marks9172 ,

You can achieve your goal by creating a new calculated column by this DAX:

ADMISSIONS - SCANNED = 
IF (
    NOT ISBLANK('YourTable'[Product Name]),
    1 / CALCULATE(
        COUNTROWS('YourTable'),
        'YourTable'[Customer Number] = EARLIER('YourTable'[Customer Number]),
        'YourTable'[Product Name] = EARLIER('YourTable'[Product Name]),
        'YourTable'[Created At Date] = EARLIER('YourTable'[Created At Date])
    ),
    0
)

Your output will look like this:

[Marks9172]

Thanks, this did the trick!

[Anonymous]

Hi, @Marks9172 

Thanks for reaching out to the Microsoft fabric community forum.

Regarding the issue you raised, my solution is as follows:

I might need to inform you that currently, I am unable to open the data you shared.

 If you need to share data with us, I recommend using GitHub and ensuring that sensitive information is removed.

However, based on your description, I have created the following test data:

And converted your Excel data into the following measures:

Admissions-Scanned = 
IF (
    NOT ( ISBLANK (MAX('Table'[Product Name] ) )),
    1 / CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT('Table','Table'[Customer Number],'Table'[Created At Date],'Table'[Product Name])
    ),
    0
)

Here's my final result, which I hope meets your requirements.
 

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[Marks9172]

Thanks for the heads up on not being able to use the sample data set.  I will use my github repository next time.  I did test the solution and it seems to work.  Thanks again.