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.
Hey,
I'm trying to add a new computed column, that will help me to indicate DISTINCT CustID.
For the example: the new coulmn will bring me "1" on lines 1, 2 and 5. if ill sum it up, ill get 3 uniqe customers.
Thanks!
Solved! Go to Solution.
@Anonymous - You could do the following steps:
1. Order by Customer.
2. Add an index column starting from 0, and another starting from 1.
3. Merge the table with itself, joining the one index column to the other. This way, you can retrieve the previous customer.
4. Add a Custom Column that returns 0 if the current and previous customers are the same, otherwise 1.
Using an Excel formula (if the source is a spreadsheet) is easier:
1. Order by Customer.
2. Add a new column, with the following formula (Assumes that CustomerID is in Column A):
=IF(A2=A1,0,1)
Hope this helps,
Nathan
@Anonymous -
You could use DISTINCTCOUNT function in a Measure to get the distinct count. That way, you would always get the correct count, regardless of the filter context. If you have a one in some rows and not others, the "1" row may be filtered out in some situations, producing a wrong result.
Distinct Customers = DISTINCTCOUNT('Sales'[CustID])
Hope this helps,
Nathan
Hey @Anonymous Thanks, I'm using PowerQuery through the excel and not through the desktop app.
i'm aware about the wrong result the may be, but im still need it on this way. Thanks!
@Anonymous - You could do the following steps:
1. Order by Customer.
2. Add an index column starting from 0, and another starting from 1.
3. Merge the table with itself, joining the one index column to the other. This way, you can retrieve the previous customer.
4. Add a Custom Column that returns 0 if the current and previous customers are the same, otherwise 1.
Using an Excel formula (if the source is a spreadsheet) is easier:
1. Order by Customer.
2. Add a new column, with the following formula (Assumes that CustomerID is in Column A):
=IF(A2=A1,0,1)
Hope this helps,
Nathan
@Anonymous wrote:@Anonymous - You could do the following steps:
1. Order by Customer.
2. Add an index column starting from 0, and another starting from 1.
3. Merge the table with itself, joining the one index column to the other. This way, you can retrieve the previous customer.
4. Add a Custom Column that returns 0 if the current and previous customers are the same, otherwise 1.
Using an Excel formula (if the source is a spreadsheet) is easier:
1. Order by Customer.
2. Add a new column, with the following formula (Assumes that CustomerID is in Column A):
=IF(A2=A1,0,1)Hope this helps,
Nathan
Finally succeed, Thanks so much!
Thanks @Anonymous ,
I think it will help and i'm almost there.
At step 2 (i'm using PowerQuery) - you meant a column with ascending series (starting from 0) or all the column be with "0"?
EDIT:
Got you about the index coulmn, but which coulmn I need retrive after I merged?
Thanks!
@Anonymous - Please refer to the following screenshot:
EDIT: The column you need is CustID, so you can compare the current row with the previous row.
Thanks @Anonymous , I edited my previous reply above.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |