cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jess303
Regular Visitor

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

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

 

 

 Example.PNG 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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

edhans_0-1648776690612.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
NabihaF
Frequent Visitor

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:

NabihaF_0-1656421777247.png

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-kalyj-msft
Community Support
Community Support

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.

vkalyjmsft_0-1649306672119.png

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
Super User
Super User

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

edhans_0-1648776690612.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Jess303
Regular Visitor

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. 

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

edhans_0-1648823621343.png

 

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors