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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PSB
Helper III
Helper III

Unique count by condition in DAX or Power Query

DAX command to count unique value in Source column based on if how many unique values in S_Cell Column.

For example in below case, "Source_Cell" Values are same in all three rows but "S_Cell" has two different values. So the count should be two. if all rows of Source_Cell and S_Cell has same values then count should be 1.

S_CellSource_CellCount
1231264902
1231264902
4561264903
ABCDAAAA1
ABCDAAAA1
ABCDAAAA1

 

 

1 ACCEPTED SOLUTION
Shaurya
Memorable Member
Memorable Member

Hi @PSB,

 

I suggest you create a table that groups your Source_Cell and shows you the distinct count of S_Cell.

 

Unique = SUMMARIZE('Table','Table'[Source_Cell],"Cell Count",DISTINCTCOUNT('Table'[S_Cell]))

 

This will give you the desired output.

 

Screenshot 2022-10-07 211839.jpg

 

If you want to see these numbers in your original table itself, you can always do a LOOKUP like:

 

CellCount = LOOKUPVALUE('Unique'[Cell Count],'Unique'[Source_Cell],'Table'[Source_Cell])

 

Screenshot 2022-10-07 211858.jpg

 

Mark this post as a solution if that works for you!

View solution in original post

4 REPLIES 4
Shaurya
Memorable Member
Memorable Member

Hi @PSB,

 

I suggest you create a table that groups your Source_Cell and shows you the distinct count of S_Cell.

 

Unique = SUMMARIZE('Table','Table'[Source_Cell],"Cell Count",DISTINCTCOUNT('Table'[S_Cell]))

 

This will give you the desired output.

 

Screenshot 2022-10-07 211839.jpg

 

If you want to see these numbers in your original table itself, you can always do a LOOKUP like:

 

CellCount = LOOKUPVALUE('Unique'[Cell Count],'Unique'[Source_Cell],'Table'[Source_Cell])

 

Screenshot 2022-10-07 211858.jpg

 

Mark this post as a solution if that works for you!

is this custom query command. I don't see lookupvalue option here.

Shaurya
Memorable Member
Memorable Member

Hi @PSB,

 

These are DAX Commands. The first one is for creating a new table with your Source_Cell column and count of unique S_Cell values.

 

The second one is for pulling that count column in your original table. So in your original table, you will have to create a new column and use the second formula that is LOOKUP.

 

Did I answer your question? Mark this post as a solution if I did!

AilleryO
Memorable Member
Memorable Member

Hi,

The easiest way to do that would be to create a column (with Power Query or DAX, but would recommend PQ) to merge the value of S_Cell and Source_Cell and then do your unique count on that column.

If you cannot do that, maybe you can group your lines using Power Query (on S_cell for instance)a nd then count the diffrent values by S_Cell ?

I let other members contribute to this post as there might be many other solutions 🙂

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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