Forum Discussion

BHAVIKCHAWLA's avatar
BHAVIKCHAWLA
New Member
2 years ago
Solved

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
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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!

     

3 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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's avatar
      BHAVIKCHAWLA
      New Member
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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!