Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I have data similar to the following:
ExampleTable1
UniqueID | Qualified | Start Date |
A | TRUE | 25/01/2020 |
A | FALSE | 20/01/2020 |
A | TRUE | 14/02/2020 |
B | TRUE | 17/03/2020 |
C | FALSE | 13/04/2019 |
D | FALSE | 13/04/2020 |
[Start Date] is related to another table/column 'Calendar'[Date] (a list of all dates up to current day)
What I want to do is:
1. get UniqueID's that only occur once in the table (i.e. 'B', 'C', and 'D' in table above)
2. from that, filter rows that have qualification status equal to FALSE (i.e. 'C', 'D') and have occured in previous years to the current year (i.e. 'C')
3. count the number of rows in the table
So the result should be '1', as row containing ID 'C' is the only one to satify all critiera.
I've tried using SUMMARIZE for Step 1, but then I run into the problem of not knowing how to apply Step 2 to that data as I no longer have the columns I need to filter by.
Solved! Go to Solution.
Hi @lightw0rks ,
I've done this in two steps - first added Count of UniqueID in Power Query, then a measure.
// Call this table ffTable
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQoJCnUFUkam+gaG+kYGRgZKsToQGTdHn2CwlAGGFFSToYm+gRFCxglJxlzfwBgh44xknKGxvoEJUMrQEizlgkUKpCsWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UniqueID = _t, Qualified = _t, #"Start Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueID", type text}, {"Qualified", type logical}, {"Start Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"UniqueID"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"data", each _, type table [UniqueID=text, Qualified=logical, Start Date=date]}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Qualified", "Start Date"}, {"Qualified", "Start Date"})
in
#"Expanded data"
In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. Name this table ffTable. You can then follow the steps I took to complete this.
Measure:
yourMeasure =
CALCULATE(
COUNT(ffTable[UniqueID]),
ffTable[Count] = 1,
ffTable[Qualified] = FALSE(),
YEAR(ffTable[Start Date]) < YEAR(today())
)
This gives me the following output:
Pete
Proud to be a Datanaut!
@lightw0rks - Perhaps:
Measure =
VAR __Table = DISTINCT(SELECTCOLUMNS(FILTER(SUMMARIZE('Table',[UniqueID],"__Count",COUNTROWS('Table'),[__Count]=1),"UniqueID",[UniqueID]))
RETURN
COUNTROWS(FILTER('Table',YEAR([Start Date]) = YEAR(TODAY())-1 && [Unique ID] IN __Table))
Thanks for your solutions guys. @amitchandak sorry i should have been more clear in my OP, each of the steps were part of a single calculation, not separate ones.
@lightw0rks - Perhaps:
Measure =
VAR __Table = DISTINCT(SELECTCOLUMNS(FILTER(SUMMARIZE('Table',[UniqueID],"__Count",COUNTROWS('Table'),[__Count]=1),"UniqueID",[UniqueID]))
RETURN
COUNTROWS(FILTER('Table',YEAR([Start Date]) = YEAR(TODAY())-1 && [Unique ID] IN __Table))
Hi @lightw0rks ,
I've done this in two steps - first added Count of UniqueID in Power Query, then a measure.
// Call this table ffTable
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQoJCnUFUkam+gaG+kYGRgZKsToQGTdHn2CwlAGGFFSToYm+gRFCxglJxlzfwBgh44xknKGxvoEJUMrQEizlgkUKpCsWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UniqueID = _t, Qualified = _t, #"Start Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueID", type text}, {"Qualified", type logical}, {"Start Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"UniqueID"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"data", each _, type table [UniqueID=text, Qualified=logical, Start Date=date]}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Qualified", "Start Date"}, {"Qualified", "Start Date"})
in
#"Expanded data"
In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. Name this table ffTable. You can then follow the steps I took to complete this.
Measure:
yourMeasure =
CALCULATE(
COUNT(ffTable[UniqueID]),
ffTable[Count] = 1,
ffTable[Qualified] = FALSE(),
YEAR(ffTable[Start Date]) < YEAR(today())
)
This gives me the following output:
Pete
Proud to be a Datanaut!
@lightw0rks , Try
1. Try with UniqueID this measure
measure = countx(filter(summarize(all(table), table[UniqueID], "_1",count(Table[UniqueID])),[_1]=1),[UniqueID])
2.
measure =
var _max = date(year(today())-1,1,1)
return
calculate(coutrows(Table), filter(Table,Year(Table[Date]) >= _max))
3.
coutrows(Table),
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
131 | |
110 | |
64 | |
55 |