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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Msri
Regular Visitor

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

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

DateID_ColumnSubID_ColumnRating
11/1/2024123456Good
11/1/2024123678Avg
11/1/2024123666Bad
11/8/2024234455Good
11/1/2024345455Good
11/1/2024345890Good
11/8/2024567900Avg
11/1/2024567900Good

Thanks in advance for your time.

1 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

vbofengmsft_0-1731650438829.png

After

vbofengmsft_1-1731650456014.png

 

Best Regards,

Bof

View solution in original post

3 REPLIES 3
v-bofeng-msft
Community Support
Community Support

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

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)

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

vbofengmsft_0-1731650438829.png

After

vbofengmsft_1-1731650456014.png

 

Best Regards,

Bof

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Kudoed Authors