Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
stratocast85
Frequent Visitor

Calculate # Employees with Unique Entries by Month

Hi Everyone,

 

Here is the type of table I have:

 

DateNameLocation
15/Apr/22 09:38BobA Sector
15/Apr/22 12:15BobA Sector
15/Apr/2216:23BobA Sector
16/Apr/22 13:14BobA Sector
16/Mar/22 07:21BobA Sector
17/Feb/22 20:08BobA Sector
15/Apr/22 13:12KarenA Sector
15/Mar/22 19:45KarenA Sector
15/Mar/22 04:21KarenA Sector
16/Feb/22 02:12KarenA Sector

 

I also have a calendar table. The table is setup so that each time someone badges in, it creates a new row.

 

I want to know, each month, how many different people came in 1 day, 2 days, 3 days, 4 days, etc. So the output would be something like this:

Month# Days Logged# People who logged these amount of days
Feb 22145
Feb 22212
Feb 22367
Feb 22434
Feb 22598
Feb 22629
Mar 22112
Mar 22278
Mar 22312

 

and so on. I have the total distinct entries, and total distinct entries by month (or any other time period), but I can't seem to create the table above! Appreciate any help.

 

 

1 ACCEPTED SOLUTION

@stratocast85 , oh, I think You need dynamic bucketing

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

 

or

 

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

View solution in original post

4 REPLIES 4
v-rongtiep-msft
Community Support
Community Support

Hi @stratocast85 ,

 I have created a simple sample, please refer to my pbix file to see if it helps you.

Create a column first.

Column =
VAR _mintime =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER ( ( 'Table' ), 'Table'[Name] = EARLIER ( 'Table'[Name] ) )
    )
RETURN
    IF ( 'Table'[Date] = _mintime, 1, 0 )

Then create a measure.

Measure_count =
CALCULATE (
    COUNT ( 'Table'[Column] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Column] <> 0
            && 'Table'[Date] = SELECTEDVALUE ( 'Table'[Date] )
    )
)

vpollymsft_0-1650610531541.png

If I have misunderstood your meaning, please provide your desired output with more details and your pbix file without privacy information.

 

Best Regards

Community Support Team _ Polly

 

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

 

 

 

amitchandak
Super User
Super User

@stratocast85 , Create a date column without time

 

Date 1 = datevalue([date])

 

them create a measure like this and use

 

distinctcount(Table[Name])

@amitchandak thanks for the response! I have that column with just a date and the distinct count, however the output just gives me the number of distinct entries per day/month/year etc. and not the different total sums for the time period.

 

What I'm looking for: How many people came in once during the period? Twice? Three times? Four times? etc.

@stratocast85 , oh, I think You need dynamic bucketing

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

 

or

 

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors