Forum Discussion
Helper IIIUnique 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_Cell | Source_Cell | Count |
| 123 | 126490 | 2 |
| 123 | 126490 | 2 |
| 456 | 126490 | 3 |
| ABCD | AAAA | 1 |
| ABCD | AAAA | 1 |
| ABCD | AAAA | 1 |
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.
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])Mark this post as a solution if that works for you!
4 Replies
- Shaurya
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.
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])Mark this post as a solution if that works for you!
PSBis this custom query command. I don't see lookupvalue option here.
- Shaurya
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
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 🙂
