Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
79 | |
73 | |
56 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |