Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
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])
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
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?
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])