cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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:

 ID Status A12 FINISHED C67 FINISHED D54 STARTED C12 FINISHED A12 FINISHED
3 ACCEPTED SOLUTIONS
Super User

Hi @AI14

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

Helper III

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" )

Super User

try replacing && with ||

4 REPLIES 4
Super User

Hi @AI14

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

Helper III

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" )

Super User

try replacing && with ||

Super User

hi @AI14

try to create a measure like this:

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.