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.
Hello everyone,
I would like to ask for some help with something I couldn't manage to do or find in this forum, even if it looks like a very simple thing.
I have a table with [Client], [Date] and [$Bought]. What I want is to create a table that only contains the rows of the first [Date] of each month that the [Client] appear (which means the client bought something).
The following image represents the original table and the one I want to create:
(The table is pretty big so I can't just remove specific rows in the Query Editor)
I couldn't manage to do this either as a calculated table or as a filter in the Query Editor...
Thanks in advance!
Solved! Go to Solution.
Hi @aryszpic,
We can create a calculated column based on your original table.
Column = IF ( 'Table'[Date] = CALCULATE ( MIN ( 'Table'[Date] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Client] ), 'Table'[Month] = EARLIER ( 'Table'[Month] ) ) ), 1, 0 )
Then create a new calculated table based on the original one.
Table1 = CALCULATETABLE('Table',FILTER('Table','Table'[Column]=1))
For more details, please check the pbix as attached.
Regards,
Frank
Hello everyone,
I would like to ask for some help with something I couldn't manage to do or find in this forum, even if it looks like a very simple thing.
I have a table with [Client], [Date] and [$Bought]. What I want is to create a table that only contains the rows of the first [Date] of each month that the [Client] appear (which means the client bought something).
The following image represents the original table and the one I want to create:
I couldn't manage to do this either as a calculated table or as a filter in the Query Editor...
Thanks in advance!
Hi @aryszpic,
We can create a calculated column based on your original table.
Column = IF ( 'Table'[Date] = CALCULATE ( MIN ( 'Table'[Date] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Client] ), 'Table'[Month] = EARLIER ( 'Table'[Month] ) ) ), 1, 0 )
Then create a new calculated table based on the original one.
Table1 = CALCULATETABLE('Table',FILTER('Table','Table'[Column]=1))
For more details, please check the pbix as attached.
Regards,
Frank
Worked wonderfully, thanks!
Measure = MIN([Date])
If you want an actual calculated table and not just the above in a table visualization along with client and month, then:
Table = SUMMARIZE(Table,[Client],[Month],"_MinDate",MIN([Date]))
That last one might need tweaked or you might have to do it with a GROUPBY, I didn't test it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |