Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Solved! Go to 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
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
After
Best Regards,
Bof
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
8 |
User | Count |
---|---|
30 | |
26 | |
15 | |
13 | |
10 |