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
ashaykohad
Helper I
Helper I

Need to find count of "Frequency occurrence" greater than 1

Need to count frequency of Occurrence before certain date

Example,

Input
Name          Date           
             
Test1           29-01-2022               
Test2           30-01-2022               
Test3           30-01-2022               
Test1           30-01-2022               
Test1           31-01-2022               
Test4           01-02-2022               
Test3           01-02-2022               
Test5           02-02-2022


Name          Date                        count Frequency (Name)
                                                   (this column is not availble in the table and caluculatin this is not working with 5L+ Entries)

Test1           29-01-2022               1
Test2           30-01-2022               1
Test3           30-01-2022               1
Test1           30-01-2022               2
Test1           31-01-2022               3
Test4           01-02-2022               1
Test3           01-02-2022               2
Test5           02-02-2022               1

Required Output
Count of Frequency greater than 1 = 3

Thanks in Advance.

1 ACCEPTED SOLUTION
JirkaZ
Solution Specialist
Solution Specialist

This should be just a simple count of rows with specific criteria.

Frequency := 

VAR _Test = Table[Name]
VAR _Date = Table[Date]
RETURN

CALCULATE(COUNTROWS(ALL(MyTable)), 
MyTable[Name] = _Test,

MyTable[Date] <= _Date)

View solution in original post

5 REPLIES 5
JirkaZ
Solution Specialist
Solution Specialist

This should be just a simple count of rows with specific criteria.

Frequency := 

VAR _Test = Table[Name]
VAR _Date = Table[Date]
RETURN

CALCULATE(COUNTROWS(ALL(MyTable)), 
MyTable[Name] = _Test,

MyTable[Date] <= _Date)

didn't understand MyTable??
Is this measure or  formula for column?

 

JirkaZ
Solution Specialist
Solution Specialist

It's a formula for a custom column. And since I don't know how your table is called, I used "MyTable" instead. 

amitchandak
Super User
Super User

@ashaykohad , Create  a measure like

Sumx(filter(addcolumns(summarize( Table, Table[Name], Table[Date]), "_1", count(Table[Name])), [_1] >1), [Test])

What is last [Test]!!

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.