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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Jstitch
Regular Visitor

How to count all the non duplicated values in a table and filter by another value

So i have this table and i have a running list of all the fluid ends as they come in.  When we recieve them we set them to on the floor.  When they are installed we add another row in order not to run through the data to find the previous row with the serial number and update the status.  What we have here is the serial numbers and status running on and on.  What i want is to get the count of fluid ends (pretty much off of the serial numbers) that are on the floor.  Which if they serial numbers have not been entered at a later time in the table they should still be on the floor which means the serial number value has not been duplicated.

 

F.E. S/N               Status

1111                   On the Floor

2222                   On the Floor

3333                   On the Floor

1111                   Installed on Pump

2222                   Sent to refurbish

4444                   On the floor

5555                   On the floor

3333                   Installed on pump

 

 

so from this table i should have only 2 units on the floor since 1111,2222,3333 have been installed on pumps or refurbished.

 

Hope this makes sense

 

thank you for the help

 

1 ACCEPTED SOLUTION

Hi @Jstitch

 

I get count 2 with your sample data. Could you share your screen shot or file?

See the pic below

 

non duplicated.png

View solution in original post

4 REPLIES 4
Zubair_Muhammad
Community Champion
Community Champion

Hi @Jstitch

 

Try this MEASURE

 

Measure =
COUNTROWS (
    FILTER (
        VALUES ( Table1[F.E. S/N] ),
        CALCULATE ( COUNT ( Table1[Status] ) ) = 1
    )
)

i inputed this and it is still showing me the count of all on the floor values basically showing 5 instead of 2

Hi @Jstitch

 

I get count 2 with your sample data. Could you share your screen shot or file?

See the pic below

 

non duplicated.png

@Jstitch

 

Also you can use this MEASURE to get the Names of those fluid ends

 

Measure 2 =
CONCATENATEX (
    FILTER (
        VALUES ( Table1[F.E. S/N] ),
        CALCULATE ( COUNT ( Table1[Status] ) ) = 1
    ),
    Table1[F.E. S/N],
    ", "
)

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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