Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
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!
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!
is this custom query command. I don't see lookupvalue option here.
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!
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 🙂
User | Count |
---|---|
103 | |
87 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |