Forum Discussion
Group by first date
I am tryingto find the % of members who use the partner as their first partner based on date. Then I want to visuzalize this.
10% use X as their first
30% use XXX as their first.
| PartnerName | Purchase Date | Member Number |
| X | 2022-02-21 | 0040 |
| XX | 2020-01-30 | 3395 |
| XXX | 2021-02-12 | 0520 |
| XXXX | 2020-09-28 | 0146 |
| XX | 2019-10-05 | 4640 |
| XX | 2020-11-05 | 0955 |
| XXX | 2019-10-02 | 3786 |
| XXXX | 2021-08-25 | 5406 |
| X | 2019-10-02 | 4703 |
| XX | 2020-09-28 | 4365 |
| XXXX | 2021-01-21 | 5435 |
| XX | 2020-03-20 | 5068 |
| XXX | 2021-11-03 | 4907 |
| XX | 2020-07-07 | 5355 |
| XXX | 2021-10-22 | 4641 |
| XXXX | 2020-06-05 | 0076 |
| XXX | 2019-12-17 | 5694 |
| XX | 2020-10-06 | 4939 |
| XXX | 2021-08-18 | 0185 |
| XXX | 2019-10-12 | 1005 |
| XX | 2021-02-17 | 5223 |
| XX | 2020-09-29 | 4658 |
| XXX | 2020-10-01 | 3037 |
| x | 2020-09-27 | 3735 |
Hi Anonymous ,
According to your statement, I think there should be multiple parentnames in each member. You want to get the percentage of first parent by date for each member. I suggest you to create a virtual table with only firstdate and parentname in firstdate for each member.
Measure:
Measure = VAR _MinDateParent_Table = SUMMARIZE ( 'Table' , 'Table'[Member Number], "MinDate", CALCULATE ( MIN ( 'Table'[Purchase Date] ) ), "ParentName", CALCULATE ( MAX ( 'Table'[PartnerName] ), FILTER ( 'Table', 'Table'[Purchase Date] = MIN ( 'Table'[Purchase Date] ) ) ) ) VAR _Membercount = CALCULATE(DISTINCTCOUNT('Table'[Member Number]),ALL('Table')) VAR _COUNT_MemberbyParent = COUNTAX(_MinDateParent_Table,[Member Number]) RETURN DIVIDE(_COUNT_MemberbyParent,_Membercount)Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi Anonymous ,
Depends if you need to use dax or PQ.
I would do the transformation probably in PQ since it's quite easy to do.
Try to use this code:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFbCsQgDEX30u8GbhLjYycDQ/e/jUnUgVYL/sjl5HHy/R6f4zwEIgQhYf8ACcd1ejIjEJgU/lFtNqOZcWAsgZn8sRvYSGqEnPK9JjdiT80/KW/tmEeEZs92E4t2Wmpe2jlVSQK0hBmuWCrQdbk5Y9Jse0UeUiyprZyShBRDrquU2ECjZkNZseIvMLXNpU8pMpzw7jJPKyh5s+JH6EVzS6vMIPso2rbbVeJxnvomut+Vgefm4+S9m8ibzNY3sEXKmCRcKrTcrjOp0o8alq8f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PartnerName = _t, #"Purchase Date" = _t, #"Member Number" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"PartnerName", type text}, {"Purchase Date", type date}, {"Member Number", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"PartnerName"}, {{"Member Number", each List.Sum([Member Number]), type nullable number}, {"MinDate", each List.Min([Purchase Date]), type nullable date}}) in #"Grouped Rows"Hi,
Try to check my PBIX file.
I have creaed custom column, which calculate the MIN date for every PartnerName, so you can easily identify the first date.
Hi Anonymous ,
According to your statement, I think there should be multiple parentnames in each member. You want to get the percentage of first parent by date for each member. I suggest you to create a virtual table with only firstdate and parentname in firstdate for each member.
Measure:
Measure = VAR _MinDateParent_Table = SUMMARIZE ( 'Table' , 'Table'[Member Number], "MinDate", CALCULATE ( MIN ( 'Table'[Purchase Date] ) ), "ParentName", CALCULATE ( MAX ( 'Table'[PartnerName] ), FILTER ( 'Table', 'Table'[Purchase Date] = MIN ( 'Table'[Purchase Date] ) ) ) ) VAR _Membercount = CALCULATE(DISTINCTCOUNT('Table'[Member Number]),ALL('Table')) VAR _COUNT_MemberbyParent = COUNTAX(_MinDateParent_Table,[Member Number]) RETURN DIVIDE(_COUNT_MemberbyParent,_Membercount)Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
