cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors