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

[Msri]

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.

[Anonymous]

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

[Msri]

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)

[Anonymous · Accepted solution]

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