Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
Jess303
Frequent 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-yanjiang-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

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
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors