Not able to calculate distinct count

[BHAVIKCHAWLA]

Hi All,

I am facing issue in calculating distinct count. I am sharing sample data with you.
First sheet is date table which I have created.
4th sheet is output I need.

I cant see option to attach file
1- 

matchDate	year
Apr 18, 2021	2021
Apr 14, 2021	2021
Apr 19, 2022	2022
Apr 16, 2022	2022
Apr 20, 2023	2023
Apr 15, 2023	2023

2-

matchDate	match_id
Apr 18, 2021	T202791
Apr 14, 2021	T202204
Apr 19, 2022	T204535
Apr 16, 2022	T205793
Apr 20, 2023	T209410
Apr 15, 2023	T208157

3-

match_id	batsmanName
T202791	ViratKohli
T202791	KyleJamieson
T202204	ViratKohli
T202204	KyleJamieson
T204535	FafduPlessis
T204535	ViratKohli
T205793	FafduPlessis
T205793	ViratKohli
T209410	ViratKohli
T209410	FafduPlessis
T208157	ViratKohli
T208157	FafduPlessis

output - 

Batsman	No of Years
ViratKohli	3
ABdeVilliers	1
KylieJamieson	1
FafduPlessis	2

[dufoq3]

Hi @BHAVIKCHAWLA,

you have to replace codes for Table1, Table2 and Table3 with your tables refereces

Result

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoUjC00FEwMjAyVNJRAlOxOlBxExzilmBxI4i4EULcDLu4kQFY3BgiboxQb4omHgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [matchDate = _t, year = _t]),
    ChangedTypeT1 = Table.TransformColumnTypes(Table1,{{"matchDate", type date}}),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcsxDoAwCEDRqzTMHYCClNE7uDXcwXj/QdMaW6c/vPzWYD+vRDUnRibIcDw1J4j8kvyIUSZ5Jx4kWnTStpKal48YO5VBLoTz0pUqqUHEDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [matchDate = _t, match_id = _t]),
    ChangedTypeT2 = Table.TransformColumnTypes(Table2,{{"matchDate", type date}}),
    Table3 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjEyMDK3NFTSUQrLLEos8c7PyMlUitVBlvCuzEn1SszNTC3Oz4NLGRmYYNcDkcCmx8TU2BQo5ZaYllIakJNaXJxZjCaFaZypuaUxDj1QKUw9liaGBvgksBlmYWhqjlUPVAJVTywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [match_id = _t, batsmanName = _t]),
    MergedQueriesT1_T2 = Table.NestedJoin(ChangedTypeT1, {"matchDate"}, ChangedTypeT2, {"matchDate"}, "Table2", JoinKind.LeftOuter),
    ExpandedTable2 = Table.ExpandTableColumn(MergedQueriesT1_T2, "Table2", {"match_id"}, {"match_id"}),
    MergedQueriesT2_T3 = Table.NestedJoin(ExpandedTable2, {"match_id"}, Table3, {"match_id"}, "Table3", JoinKind.LeftOuter),
    ExpandedTable3 = Table.ExpandTableColumn(MergedQueriesT2_T3, "Table3", {"batsmanName"}, {"batsmanName"}),
    GroupedRows = Table.Group(ExpandedTable3, {"batsmanName"}, {{"No of Years", each List.Count(List.Distinct([year])), Int64.Type}})
in
    GroupedRows

[BHAVIKCHAWLA]

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?

[Anonymous · Accepted solution]

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!