topic Finding count of rows in subgroup and flagging when there is only one row in the group in DAX Commands and Tips

Finding count of rows in subgroup and flagging when there is only one row in the group

Msri — Thu, 14 Nov 2024 20:19:52 GMT

Hi,

I am looking for a measure that gives me the count when there is only one row with Rating = "Good" for every ID.

Every ID can have multiple Sub_ID. Sub_ID are shared among IDs. Rating is for the ID column.

From the below example for 11/1/2024, only 567 is counted, for 11/8/2024, 234 is counted

Date	ID_Column	SubID_Column	Rating
11/1/2024	123	456	Good
11/1/2024	123	678	Avg
11/1/2024	123	666	Bad
11/8/2024	234	455	Good
11/1/2024	345	455	Good
11/1/2024	345	890	Good
11/8/2024	567	900	Avg
11/1/2024	567	900	Good

Thanks in advance for your time.

Re: Finding count of rows in subgroup and flagging when there is only one row in the group

Anonymous — Fri, 15 Nov 2024 02:29:38 GMT

Hi Msri ,

Do you need to group the data based on 'Date' and 'ID_Column' and then calculate the number of groups that contain only one 'Good' item? This would provide a table showing the date and count. If that’s the case, could you clarify why the entry for '123' on November 1st is not included? It has only one 'Good' on that day. Additionally, could you provide a sample of the result you’re aiming for?

Best Regards,

Bof

Re: Finding count of rows in subgroup and flagging when there is only one row in the group

Msri — Fri, 15 Nov 2024 04:22:31 GMT

Hi Bof,

Thanks for replying, Date is in the filters so i dont essentially need to group by date in the DAX. I need a count of ID_Column when the count(SubID_Column) = 1 and its Rating = Good. 123 was not counted as it had Sub_ID count = 3. The measure should return the distinct ID_Column count. This count is then used in the table that is split by Zones. (SQL has zones aligned for every ID_column)

Re: Finding count of rows in subgroup and flagging when there is only one row in the group

Anonymous — Fri, 15 Nov 2024 06:01:13 GMT

Hi Msri ,

Because your request involves complex grouping operations, I have used Power Query to implement this.

let Source = Table.FromRecords({ [Date="11/1/2024", ID_Column=123, SubID_Column=456, Rating="Good"], [Date="11/1/2024", ID_Column=123, SubID_Column=678, Rating="Avg"], [Date="11/1/2024", ID_Column=123, SubID_Column=666, Rating="Bad"], [Date="11/8/2024", ID_Column=234, SubID_Column=455, Rating="Good"], [Date="11/1/2024", ID_Column=345, SubID_Column=455, Rating="Good"], [Date="11/1/2024", ID_Column=345, SubID_Column=890, Rating="Good"], [Date="11/8/2024", ID_Column=567, SubID_Column=900, Rating="Avg"], [Date="11/1/2024", ID_Column=567, SubID_Column=900, Rating="Good"] }), GroupedRows = Table.Group(Source, {"ID_Column"}, { {"GroupedData", each _, type table [Date=nullable date, ID_Column=number, SubID_Column=number, Rating=text]} }), AddGoodAndCount = Table.AddColumn(GroupedRows, "FilteredData", each let GroupData = [GroupedData], DistinctSubID = Table.Distinct(GroupData, {"SubID_Column"}), FilterGood=Table.SelectRows(GroupData,each [Rating]="Good"), CountGood=Table.RowCount(FilterGood), CountSubID = Table.RowCount(DistinctSubID) in if CountGood = 1 and CountSubID=1 then Table.Distinct(FilterGood, {"SubID_Column"}) else null ), #"Expanded FilteredData" = Table.ExpandTableColumn(AddGoodAndCount, "FilteredData", {"Date", "ID_Column", "SubID_Column", "Rating"}, {"Date", "WaitForRemove", "SubID_Column", "Rating"}), FilterNull=Table.SelectRows(#"Expanded FilteredData",each [Rating] <> null), RemovColumns=Table.RemoveColumns(FilterNull,{"GroupedData","WaitForRemove"}) in RemovColumns

Before

After

Best Regards,

Bof