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

Get 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

Reply
lightw0rks
Frequent Visitor

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

Hi,

 

I have data similar to the following:
ExampleTable1

UniqueIDQualifiedStart Date
ATRUE25/01/2020
AFALSE20/01/2020
ATRUE14/02/2020
BTRUE17/03/2020
CFALSE13/04/2019
DFALSE13/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.

2 ACCEPTED SOLUTIONS
BA_Pete
Super User
Super User

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:

BA_Pete_0-1598272050962.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
lightw0rks
Frequent Visitor

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
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
BA_Pete
Super User
Super User

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:

BA_Pete_0-1598272050962.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.