create new column to distinct count the number of value in each rows

[Haidannx]

Hi,

I have a data set like below

Request ID	Provider	Price
1	HMS	4$
1	TMS	7$
1	TMS	4$
1	HMS	11$
2	TMS	1$
2	TMS	2$
3	HMS	1$

I`d like to create a new column to distinct count "Provider" with the expected outcome like below:

Request ID	Provider	Price	Count
1	HMS	4$	2
1	TMS	7$	2
1	TMS	4$	2
1	HMS	11$	2
2	TMS	1$	1
2	TMS	2$	1
3	HMS	1$	1

Please send help

With many thanks

[amitchandak]

Create a new column

new column = countx(filter(table,table[Request ID] =table[Request ID] && table[Provider] =table[Provider]),table[Request ID])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Haidannx]

i don`t know why but the result comes out as Blank

[amitchandak]

It seems like my mistake. missing earlier

new column = countx(filter(table,table[Request ID] =earlier(table[Request ID]) && table[Provider] =earlier(table[Provider])),table[Request ID])

[Haidannx]

Your DAX will tell me the how many rows are there with the same Request ID, not how many Provider does it have with the same Request id =((

can you check again your DAX?

the Provider columns`s  only 2 values whitch are HMS or TMS

[amitchandak]

Try

Column = CALCULATE(DISTINCTCOUNT(Sheet1[Provider]),FILTER(Sheet1,Sheet1[Request ID]=EARLIER(Sheet1[Request ID])))

link :https://www.dropbox.com/s/res6g1hr53edzpn/distinctCount.pbix?dl=0

[Haidannx]

I have known this DAX
However, my dataset is to large (aroun 3 mils row) to use this DAX. It`d dumped RAM

[v-yiruan-msft]

Hi @Haidannx ,

Considering the large amount of data, and creating the calculated column will take up memory, you want to check if there is another method to achieve this requirement (like create one measure) ?

Best Regards

Rena

[Haidannx · Accepted solution]

Nevermind, i`ve found the DAX useful

# of provider = CALCULATE(DISTINCTCOUNT(table[Provider]),ALLEXCEPT(table,table[request_id]))

Thanks for all of your support