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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
vinothkumar1990
Helper II
Helper II

Calculate the distinct Count

Hi,

I have a table which contains SID, State and State Code. I want to create a column which shows the distinct count of state code for each state.

Example,

Data
SIDStateState Code
1KarnatakaKA
2TamilnaduTN
3KeralaKL
4KarnatakaKA
5TamilnaduTNN
6DelhiDL
7TamilnaduTNU
8KeralaKRL

 

Expected Report
SIDStateState CodeDistictct CountComment
1KarnatakaKA11 - KA
2TamilnaduTN33 - TN, TNN and TNU
3KeralaKL22 -KL and KRL
4KarnatakaKA1Only KA
5TamilnaduTNN33 - TN, TNN and TNU
6DelhiDL11 - DL
7TamilnaduTNU33 - TN, TNN and TNU
8KeralaKRL22 -KL and KRL

 

Thanks in advance!

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

Hi , @vinothkumar1990 

According to your description, you want to get the distincount of the [State Code] prt [State] .

 [1]If you want to create a calcuated column,  you can use this dax code:

Column = CALCULATE( DISTINCTCOUNT('Table'[State Code])  ,ALLEXCEPT('Table','Table'[State])  )
Column 2 = var _t  = CALCULATETABLE( DISTINCT('Table'[State Code]) , ALLEXCEPT('Table','Table'[State]))

return
CONCATENATEX(_t , [State Code] , ",")

 

The result is as follows:

vyueyunzhmsft_0-1690162464156.png

[2]If you want to create a measre , you can use this dax code and just put the field on the visual:

Measure = var _t = DISTINCT('Table'[State Code])
return
CONCATENATEX( _t , [State Code],",")

vyueyunzhmsft_1-1690162510145.png

 

 

 

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

2 REPLIES 2
v-yueyunzh-msft
Community Support
Community Support

Hi , @vinothkumar1990 

According to your description, you want to get the distincount of the [State Code] prt [State] .

 [1]If you want to create a calcuated column,  you can use this dax code:

Column = CALCULATE( DISTINCTCOUNT('Table'[State Code])  ,ALLEXCEPT('Table','Table'[State])  )
Column 2 = var _t  = CALCULATETABLE( DISTINCT('Table'[State Code]) , ALLEXCEPT('Table','Table'[State]))

return
CONCATENATEX(_t , [State Code] , ",")

 

The result is as follows:

vyueyunzhmsft_0-1690162464156.png

[2]If you want to create a measre , you can use this dax code and just put the field on the visual:

Measure = var _t = DISTINCT('Table'[State Code])
return
CONCATENATEX( _t , [State Code],",")

vyueyunzhmsft_1-1690162510145.png

 

 

 

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

foodd
Super User
Super User

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.