Forum Discussion
DISTINCT count for first value DAX
Help me to create the calculated column for the DISTINCT count for first value DAX
if id repeats multiple time we have to consider as 1
| ID | Result |
| 69260 | 1 |
| 69260 | 0 |
| 69260 | 0 |
| 69260 | 0 |
| 69260 | 0 |
| 68440 | 1 |
| 54098 | 1 |
| 54098 | 0 |
| 54098 | 0 |
| 54098 | 0 |
| 37581 | 1 |
| 85021 | 1 |
| 72878 | 1 |
| 62765 | 1 |
| 62765 | 0 |
| 62765 | 0 |
| 62765 | 0 |
| 62765 | 0 |
| 62765 | 0 |
| 62765 | 0 |
| 59170 | 1 |
| 50076 | 1 |
| 50076 | 0 |
| 75400 | 1 |
| 37986 | 1 |
| 37986 | 0 |
| 37986 | 0 |
| 37986 | 0 |
| 37986 | 0 |
| 56591 | 1 |
| 42499 | 1 |
| 52460 | 1 |
Hi Anonymous ,
At first, you need to add an index column in the query editor.
Then you could create a new column to get the result.
Column = VAR a = CALCULATE ( FIRSTNONBLANK ( 'Table'[ID], 1 ), FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ) ) RETURN IF ( a = 'Table'[ID], 0, 1 )
13 Replies
Hi Anonymous ,
At first, you need to add an index column in the query editor.
Then you could create a new column to get the result.
Column = VAR a = CALCULATE ( FIRSTNONBLANK ( 'Table'[ID], 1 ), FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ) ) RETURN IF ( a = 'Table'[ID], 0, 1 )Hello!
How would do it so the number adds up automatically instead of counting 1 each time? So to have kind of an index without the duplicates Id in it?
Hi,
Share some data and show the expected result clearly.
amitchandak az38 TomMartens can you check this issue
Distinct count function is there for this
https://docs.microsoft.com/en-us/dax/distinctcount-function-dax
M1= distinctcount(ID) M1= Calculate (distinctcount(ID))Have I missed something?
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 Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601If you need exact same display, what you have give. Then first need a unique ID . Add index column .
https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi
In new office ribbon, edit queries is transform data
Then create a column like
Column = if(ISBLANK( COUNTX(FILTER(countDistinct,countDistinct[ID] = EARLIER(countDistinct[ID]) && countDistinct[Index]<EARLIER(countDistinct[Index])),countDistinct[ID])),1,0)refer
https://www.dropbox.com/s/m5s2lg2jpe59nap/DistinctData.pbix?dl=0
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 Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601.
