Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Nevermind, i`ve found the DAX useful
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
i don`t know why but the result comes out as Blank
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])
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
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
I have known this DAX
However, my dataset is to large (aroun 3 mils row) to use this DAX. It`d dumped RAM
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
Nevermind, i`ve found the DAX useful
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |