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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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