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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
pandeyml
Frequent Visitor

Number of new unique ID's

Hey guys,

 

I have a [Date] and a [ID] column. There are more than 10k unique ID's coming in everyday. But there was a sudden rise in the number of ID's since few days. I want to get the [measure 1] number of new unique ID's which were not present yesterday and [measure 2] same with monthly. ID's which were not present a month before. I have a calendar table placed.

Please give me some ideas how to find unique id's ?

1 ACCEPTED SOLUTION

Hi @pandeyml 

Can you please try the following

 

Measure 1 (Day) = 

var var_ReferenceDate = MAX(New_ID_Count[Date])
var var_ComparisonDate = 
        CALCULATE(
            LASTDATE(DimDate[Date]),
            FILTER(
                ALL(DimDate),
                DimDate[WeekDayNum]<> 6 && DimDate[WeekDayNum]<> 7 && DimDate[Date] < var_ReferenceDate 
            )
        )
var var_IDsReferenceDate = CALCULATETABLE(VALUES(New_ID_Count[ID]))
var var_CountOfIDsReferenceDate = COUNTROWS(var_IDsReferenceDate)
var var_IDsDayBefore = CALCULATETABLE(VALUES(New_ID_Count[ID]),New_ID_Count[Date]=var_ComparisonDate)


RETURN

var_CountOfIDsReferenceDate

-

COUNTROWS(
    NATURALINNERJOIN(var_IDsReferenceDate,var_IDsDayBefore)
)
Measure 2 (Month) = 

var var_ReferenceDate = MAX(New_ID_Count[Date])
var var_firstofMonth = EOMONTH(var_ReferenceDate,-1)
var var_ComparisonDate = 
        CALCULATE(
            LASTDATE(DimDate[Date]),
            FILTER(
                ALL(DimDate),
                DimDate[WeekDayNum]<> 6 && DimDate[WeekDayNum]<> 7 && DimDate[Date] < var_firstofMonth
            )
        )

var var_IDsReferenceDate = CALCULATETABLE(VALUES(New_ID_Count[ID]))
var var_CountOfIDsReferenceDate = COUNTROWS(var_IDsReferenceDate)
var var_IDsComparisonDay = CALCULATETABLE(VALUES(New_ID_Count[ID]),New_ID_Count[Date]=var_ComparisonDate)


RETURN
var_CountOfIDsReferenceDate

-

COUNTROWS(
    NATURALINNERJOIN(var_IDsReferenceDate,var_IDsComparisonDay)
)

 

in my case 6 and 7 are the weekend days

 

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

View solution in original post

11 REPLIES 11
Mikelytics
Resident Rockstar
Resident Rockstar

Hi @pandeyml 

Can you please check the following appraoch fort measure 1?

 

my Base Table called "New_Count_ID"

Mikelytics_0-1669539631079.png

measure for day calculation 

Measure 1 (Day) = 

var var_ReferenceDate = MAX(New_ID_Count[Date])
var var_DayBefore = var_ReferenceDate-1
var var_IDsReferenceDate = CALCULATETABLE(VALUES(New_ID_Count[ID]))
var var_CountOfIDsReferenceDate = COUNTROWS(var_IDsReferenceDate)
var var_IDsDayBefore = CALCULATETABLE(VALUES(New_ID_Count[ID]),New_ID_Count[Date]=var_DayBefore)


RETURN
var_CountOfIDsReferenceDate

-

COUNTROWS(
    NATURALINNERJOIN(var_IDsReferenceDate,var_IDsDayBefore)
)

 

Result

Mikelytics_1-1669539717615.png

 

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

 

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

@pandeyml 

 

For measure 2 I extended my sample data set

Mikelytics_2-1669540371353.png

 

and slightly adjsuted the measure

Measure 2 (Month) = 

var var_ReferenceDate = MAX(New_ID_Count[Date])
var var_ComparisonDate = EOMONTH(var_ReferenceDate,-1)
var var_IDsReferenceDate = CALCULATETABLE(VALUES(New_ID_Count[ID]))
var var_CountOfIDsReferenceDate = COUNTROWS(var_IDsReferenceDate)
var var_IDsComparisonDay = CALCULATETABLE(VALUES(New_ID_Count[ID]),New_ID_Count[Date]=var_ComparisonDate)


RETURN
var_CountOfIDsReferenceDate

-

COUNTROWS(
    NATURALINNERJOIN(var_IDsReferenceDate,var_IDsComparisonDay)
)

Mikelytics_3-1669540506660.png

 

Result

Mikelytics_4-1669540526320.png

 

Please be aware that the approach expüects that you have a list of IDs for every single day. Do you wirk with a data table or something or do you have dates without an ID list?

 

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Hey @Mikelytics ,

Appreciate your reply. It brings me closer to the exact solution.

the point that you mentioned in the end "expects a list of ID's coming everyday". I do have ID's coming everyday except for saturday and sunday. So when I use this measure it gives me all the unique ID's for monday. So eg : if there are 2000 unique ID's monday it gives me 2000 in the table. Rest of the days [tues - frid] it gives me correct values.

So my Data table includes

DATEIDPrice 1Warning Count
datetimetextfloatint (category value : 0,1,2)

Also,

Can I get the list of ID's that are new ? I'm trying but can't get the values please if you can help

 

Hi @pandeyml 

 

Do you use a proper date table with weekday numbers and weekday descriptions? Can you please show me a screenshot of your data model?

 

Best regards

Michael

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Calendar and Main table . Can See the ID columnCalendar and Main table . Can See the ID column

Yes I do have the main calendar table.

Hi @pandeyml 

Can you please try the following

 

Measure 1 (Day) = 

var var_ReferenceDate = MAX(New_ID_Count[Date])
var var_ComparisonDate = 
        CALCULATE(
            LASTDATE(DimDate[Date]),
            FILTER(
                ALL(DimDate),
                DimDate[WeekDayNum]<> 6 && DimDate[WeekDayNum]<> 7 && DimDate[Date] < var_ReferenceDate 
            )
        )
var var_IDsReferenceDate = CALCULATETABLE(VALUES(New_ID_Count[ID]))
var var_CountOfIDsReferenceDate = COUNTROWS(var_IDsReferenceDate)
var var_IDsDayBefore = CALCULATETABLE(VALUES(New_ID_Count[ID]),New_ID_Count[Date]=var_ComparisonDate)


RETURN

var_CountOfIDsReferenceDate

-

COUNTROWS(
    NATURALINNERJOIN(var_IDsReferenceDate,var_IDsDayBefore)
)
Measure 2 (Month) = 

var var_ReferenceDate = MAX(New_ID_Count[Date])
var var_firstofMonth = EOMONTH(var_ReferenceDate,-1)
var var_ComparisonDate = 
        CALCULATE(
            LASTDATE(DimDate[Date]),
            FILTER(
                ALL(DimDate),
                DimDate[WeekDayNum]<> 6 && DimDate[WeekDayNum]<> 7 && DimDate[Date] < var_firstofMonth
            )
        )

var var_IDsReferenceDate = CALCULATETABLE(VALUES(New_ID_Count[ID]))
var var_CountOfIDsReferenceDate = COUNTROWS(var_IDsReferenceDate)
var var_IDsComparisonDay = CALCULATETABLE(VALUES(New_ID_Count[ID]),New_ID_Count[Date]=var_ComparisonDate)


RETURN
var_CountOfIDsReferenceDate

-

COUNTROWS(
    NATURALINNERJOIN(var_IDsReferenceDate,var_IDsComparisonDay)
)

 

in my case 6 and 7 are the weekend days

 

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Hi @Mikelytics ,

Thankyou very for your help. I do got the values this time. But I'm not sure if they are correct. Is there a way i can get the ID's name ?? A matrix doesn't show the values it just shows names of all ID's.

 

Regards,

Shubham

 

 

 

 

sudhav
Helper V
Helper V

lets say its yiour table

sudhav_1-1669486589257.png

 

we need mainly 3 measures

1)CountofTotalUniqueIds = COUNT('IDs Table'[Unique ID])

2)countofUniqueIds_CurrentMonth = calculate(COUNT('IDs Table'[Unique ID]),FILTER('IDs Table','IDs Table'[Date].[MonthNo]=MONTH(TODAY())))
3)countofUniqueIds_Today = calculate(COUNT('IDs Table'[Unique ID]),FILTER('IDs Table','IDs Table'[Date].[Date]=TODAY()))
 
these all yoiu are expecting... let me know if anything else...
 
sudhav_2-1669486956412.png

 

Thanks for your reply. I think this will give me the count of unique ID's per day and month. But what I need is the new unique ID's which were not present yesterday but are added today. So the ID's are repeated every day but today some more new ID's are added I want to get those bro. Same with the months. Which new ID's are added this month.

Take a slicer and select relative date and select this month for the current month added IDs, then you will get list of this month added IDs which are not in the previous months

sudhav_1-1669491344622.png

Another way is take a table visual and take unique IDS, date, countofUniqueIds_CurrentMonth for current month added IDs like below

sudhav_2-1669491536407.png

 

For today added list of IDs which are not in yesterday, take same unique IDs, date,countofUniqueIds_Today in a table visual like below

 

sudhav_3-1669491628660.png

I think it make sense..... please let me know if its useful to you and mark it as solution if you get answer...

 

 

 

I think there is a misunderstanding. Everyday 10k unique ID's are incomming means the same ID's are repeated everyday. But they are unique on a daily basis. Some new unique ID's get added today and I want to get only those.The example you have shown has different ID's everyday. So I'll have to mention in the measure that the ID(today) <> ID(yesterday).

 

Hope I'm clear. Let me know what I'm Missing 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.