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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
BHAVIKCHAWLA
New Member

Not able to calculate distinct count

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- 

matchDateyear
Apr 18, 20212021
Apr 14, 20212021
Apr 19, 20222022
Apr 16, 20222022
Apr 20, 20232023
Apr 15, 20232023

2-

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

3-

match_idbatsmanName
T202791ViratKohli
T202791KyleJamieson
T202204ViratKohli
T202204KyleJamieson
T204535FafduPlessis
T204535ViratKohli
T205793FafduPlessis
T205793ViratKohli
T209410ViratKohli
T209410FafduPlessis
T208157ViratKohli
T208157FafduPlessis

output - 

BatsmanNo of Years
ViratKohli3
ABdeVilliers1
KylieJamieson1
FafduPlessis2
1 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. 

vjingzhanmsft_0-1712027677624.png

Connect two tables on match_id column. Modify the cross-filter direction to Both

vjingzhanmsft_1-1712027912718.png

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]) 

vjingzhanmsft_2-1712028004981.pngvjingzhanmsft_3-1712028088496.png

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

 

View solution in original post

3 REPLIES 3
dufoq3
Super User
Super User

Hi @BHAVIKCHAWLA,

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

 

Result

dufoq3_0-1711901606075.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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. 

vjingzhanmsft_0-1712027677624.png

Connect two tables on match_id column. Modify the cross-filter direction to Both

vjingzhanmsft_1-1712027912718.png

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]) 

vjingzhanmsft_2-1712028004981.pngvjingzhanmsft_3-1712028088496.png

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors