Count unique ID's that only occur once in table and apply additional filters

[lightw0rks]

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.

[BA_Pete · Accepted 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

[lightw0rks]

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.

[Greg_Deckler · Accepted solution]

@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))

[BA_Pete · Accepted 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

[amitchandak]

@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),