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
konijay
New Member

total count of member with more than X number of UNIQUE case ID

Member      case ID
jim                 a1
jim                 a1
jim                 a1
jim                 a2
jim                 a3
bob               b1
bob               b1
bob               b2
sam               c1
sam               c2
sam               c3

 

Hello Experts

I'd like a measure that will show

 

Total number of members with 3 and up UNIQUE case IDs, in this case = 2 (jim and sam)

 

thank you

 

 

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

hi @konijay 

try like:

Measure = 
VAR _table = 
ADDCOLUMNS(
    VALUES(TableName[Member]),
    "Count",
    CALCULATE(DISTINCTCOUNT(TableName[Case ID]))
)
RETURN
COUNTROWS(
    FILTER(_table, [Count]>=3)
)

View solution in original post

2 REPLIES 2
FreemanZ
Super User
Super User

hi @konijay 

try like:

Measure = 
VAR _table = 
ADDCOLUMNS(
    VALUES(TableName[Member]),
    "Count",
    CALCULATE(DISTINCTCOUNT(TableName[Case ID]))
)
RETURN
COUNTROWS(
    FILTER(_table, [Count]>=3)
)

LOVE U!

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