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
Anonymous
Not applicable

DISTINCTCOUNT

I accidentally closed the other post, new member.. I want to DISTINCTCOUNT "Decimal hm" column to creat an equipment runtime, and not include in the count items as attached or any with less than 10. This is so equipment maintenance testing etc is not included in the runtime. Thanks in advance, Daniel

@TheoC 

Capture.JPG

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

I think this might be what you want. It looks at all the [Decimal hm] and counts how many rows are associated with it and then filters to keep only the ones where the row count is greater than or equal to 10.

 

Runtime (hours) =
VAR Summary =
    SUMMARIZE ( Sheet1, Sheet1[decimal hm], "@Rows", COUNTROWS ( 'Sheet1' ) )
RETURN
    COUNTROWS ( FILTER ( Summary, [@Rows] >= 10 ) ) / 4

 

Here's another approach that iterates through the distinct [Decimal hm] checking the row count for each one:

Runtime (hours) =
SUMX (
    DISTINCT ( Sheet1[decimal hm] ),
    IF ( CALCULATE ( COUNTROWS ( Sheet1 ) ) >= 10, 0.25, 0 )
)

 

View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

I think this might be what you want. It looks at all the [Decimal hm] and counts how many rows are associated with it and then filters to keep only the ones where the row count is greater than or equal to 10.

 

Runtime (hours) =
VAR Summary =
    SUMMARIZE ( Sheet1, Sheet1[decimal hm], "@Rows", COUNTROWS ( 'Sheet1' ) )
RETURN
    COUNTROWS ( FILTER ( Summary, [@Rows] >= 10 ) ) / 4

 

Here's another approach that iterates through the distinct [Decimal hm] checking the row count for each one:

Runtime (hours) =
SUMX (
    DISTINCT ( Sheet1[decimal hm] ),
    IF ( CALCULATE ( COUNTROWS ( Sheet1 ) ) >= 10, 0.25, 0 )
)

 

TomMartens
Super User
Super User

Hey @Anonymous ,

 

please create pbix file that contains sample data, if you want to have a calculated column a pbix with a single table is sufficient, but it's necessary that all calculated columns are included. If you are looking for a measure than your sample file has to reflect your data model (tables, relationships, calculated columns, and relevant measures). Upload the file to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Anonymous
Not applicable

Attached file

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.