Forum Discussion
Not able to calculate distinct count
Hi BHAVIKCHAWLA
Thanks dufoq3 for the power query method. Here is a solution without using power query.
Firstly, as the second table includes match dates, we can extract years from matchDate column to have a new Year column. This can be done with either DAX or Power Query. Then we need only the second and third table in our model. The first table is not needed.
Connect two tables on match_id column. Modify the cross-filter direction to Both.
Then you can add batsmanName and Year column into a table visual, set Year's aggregation to Count(Distinct). You can also create a measure like
Played Years = DISTINCTCOUNT(Table2[Year])
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
Appreciate your efforts! but is there any way to do these through measures. My end result is filtering.. like I only want batsman who have played in all years or particular more than years.
Can you help in this?
Hi BHAVIKCHAWLA
Thanks dufoq3 for the power query method. Here is a solution without using power query.
Firstly, as the second table includes match dates, we can extract years from matchDate column to have a new Year column. This can be done with either DAX or Power Query. Then we need only the second and third table in our model. The first table is not needed.
Connect two tables on match_id column. Modify the cross-filter direction to Both.
Then you can add batsmanName and Year column into a table visual, set Year's aggregation to Count(Distinct). You can also create a measure like
Played Years = DISTINCTCOUNT(Table2[Year])
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!