Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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 )
)
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 )
)
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
Hi again Tom, see attached link - thanks
https://drive.google.com/file/d/12wXq5vZc_sfll6-N-YSM_RcW7YNwuVjF/view?usp=sharing
Attached file
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |