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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
AI14
Helper III
Helper III

measure to count dulicates

Hi

 

i want to count the total finished however there areID's that are duplicate. how do i count filter and only dount unique values of another?

 

i have a measure with the following formula:

= countx(filter(Table,Table[Status]="Finished"),Table[Status])

 

example: 

IDStatus
A12FINISHED
C67FINISHED
D54STARTED
C12FINISHED
A12FINISHED
3 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

Hi @AI14 

please try

=
CALCULATE ( DISTINCTCOUNT ( Table[ID] ), Table[Status] = "Finished" )

View solution in original post

Thanks, worked.

 

what if i wanted to add 2 filters

 

i tried the below but didnt count, just showed blank

 

CALCULATE ( DISTINCTCOUNT ( Table[ID] ), Table[Status] = "Finished"  && Table[Status] = "Started" )

View solution in original post

try replacing && with ||

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

Hi @AI14 

please try

=
CALCULATE ( DISTINCTCOUNT ( Table[ID] ), Table[Status] = "Finished" )

Thanks, worked.

 

what if i wanted to add 2 filters

 

i tried the below but didnt count, just showed blank

 

CALCULATE ( DISTINCTCOUNT ( Table[ID] ), Table[Status] = "Finished"  && Table[Status] = "Started" )

try replacing && with ||

FreemanZ
Super User
Super User

hi @AI14 

try to create a measure like this:

count = 
VAR _table =
ADDCOLUMNS(
    VALUES(TableName[ID]),
   "Status",
   CALCULATE(MAX(TableName[Status]))
)
RETURN
COUNTROWS(
    FILTER(_table, TableName[Status] = "Finished")
)

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors