Creating a new column using distinct count based off of unique value of a different column

[Jess303]

So I am new to the DAX world and this is giving me a headache. I have multiple rows of IDs and I need to count how many unique customers for each ID. Column three is what I would like to see in my table as a created column in the example below. If it is possible! Thanks

  

[NabihaF]

I have a similar query, but the problem is that my data is spread across a given time period. So the query above is not helping.

This is what my data looks like:

I have unique user IDs, the number of sessions for that ID on a given date.

Now i want to create a basic line chart which can show the cumulative number of sessions per unique user.

Please help.

[v-yanjiang-msft]

Hi @Jess303 ,

According to your description, here's my solution.

Create a calculated column.

Count of customers =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer] ),
    FILTER ( ALL ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
)

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans · Accepted solution]

This will do it without invoking CALCULATE, which can cause problems in a Calculated Column.

Customer Count = 
VAR varCurrentID = Records[ID]
VAR varCustomerCount =
    COUNTROWS(
        DISTINCT(
            SELECTCOLUMNS(
                FILTER(
                    Records,
                    Records[ID] = varCurrentID
                ),
                "@Customers", Records[Customer]
            )
        )
    )
RETURN
    varCustomerCount

[Jess303]

I get this error message. When I run the DAX:

DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values.

However both are type integer. I tried to change them to text it did not work. 

[edhans]

You need to show the info. I just redid it using all numbers and converting both to integer. My DAX didn't change.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.