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
matheus_peppers
Frequent Visitor

How to make a count of unique registrations per day?

I have the following table, where I need to make a measurement that relates the two columns:

A) CPF

B) Registration date

 

matheus_peppers_0-1672286603280.png

 

What I need to do is the following:

I have a registration database, where people register using their CPF and email, and my database records the days they registered.

 

I need to make a measure where I can count how many unique CPF's registered on each day, because I need to use this number to subtract with another measure later.

 

In other words, what I want is a measure where I can see per day how many registrations they had on the day, so if I throw this into a table I can see per day how many unique registrations they had, instead of the table showing me just the total.

 

Can someone help me with this?

1 ACCEPTED SOLUTION
SamInogic
Super User
Super User

Hi,

 

I have a created a Date Table  and added a column with the following DAX Expression

 

Date 28days = CONCATENATE(CONCATENATE('DateTable'[Date].[Day],"/"),CONCATENATE(CONCATENATE('DateTable'[Date].[MonthNo],"/"),'DateTable'[Date].[Year]))

 

this DAX returns the date in string format(using Concatenate),

SamInogic_0-1672296782447.png

 

 

I have created a SAMPLE TABLE 

 

SamInogic_1-1672296782452.png

 

 

For Desired output we have to create a Relationship between the DATE TABLE and SAMPLE TABLE

SamInogic_2-1672296782464.png

 

Now we can see the Registration count by Day

For this add the [DATE 28 DAYS] columns and [DATE THEY REGISTRATED] in a table Visuals and add COUNT to the [DATE THEY REGISTRATED] 

SamInogic_3-1672296876107.png

 

Now you can see the count of Registration by the day 

SamInogic_4-1672296876108.png

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
Community Support

Hi @matheus_peppers,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
SamInogic
Super User
Super User

Hi,

 

I have a created a Date Table  and added a column with the following DAX Expression

 

Date 28days = CONCATENATE(CONCATENATE('DateTable'[Date].[Day],"/"),CONCATENATE(CONCATENATE('DateTable'[Date].[MonthNo],"/"),'DateTable'[Date].[Year]))

 

this DAX returns the date in string format(using Concatenate),

SamInogic_0-1672296782447.png

 

 

I have created a SAMPLE TABLE 

 

SamInogic_1-1672296782452.png

 

 

For Desired output we have to create a Relationship between the DATE TABLE and SAMPLE TABLE

SamInogic_2-1672296782464.png

 

Now we can see the Registration count by Day

For this add the [DATE 28 DAYS] columns and [DATE THEY REGISTRATED] in a table Visuals and add COUNT to the [DATE THEY REGISTRATED] 

SamInogic_3-1672296876107.png

 

Now you can see the count of Registration by the day 

SamInogic_4-1672296876108.png

tamerj1
Super User
Super User

Hi @matheus_peppers 

the date column in your visual is coming from which table? What other columns are used in the same visual? How does your data model look like? Any relationships? Are you sure that the dax proposed by @FreemanZ has been used in a measure not a calculated column?

FreemanZ
Super User
Super User

hi @matheus_peppers ,

If you plot the table visual with the date column, this measure shall be enough:

Measeure =DISTINCTCOUNT(TableName[CPF])

or?

But the problem is that this measurement does not give me an iterating number. When I play in the table visual to see the number by days, it just shows me the total. I would like something that iterates, like a SUMX.

is it possible to provide a small sample of the output you would like ot see? Just the columns names and 2 to 3 rows of values will do.. having a bit of dififculty understaning what you mean by your statement above. Thanks 🙂

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