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.
I have a matrix table, where the first column is a list of countries, and the rest of the columns are the years (2001, 2002 - to present.)
The cells are filled with the product purchases for each year.
I can easily read this table, to see which was the first year the country had a product purchase. How can I simplify this to show just a column of countries and the first year with a product purchase.
So: Algeria - 2002
Bulgaria - 2015
etc.
Relevant fields are "Country", "Date of Purchase (which I have in the visual as year only" and "Purchase Amount"
Thank you.
Solved! Go to Solution.
Hi @Unicorn_Tech ,
You can create the following measure:
measure = CALCULATE(MIN(Table[Year]),FILTER(Table,Table[Country]=MAX(Table[Country])))
If it doesn't work, please show us some sample data.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@Unicorn_Tech , Product, country , MIn(Table[Year]) In the visual
or create a rank Column and filter
rankx(Filter(Table, [Product] =earlier([Product]) && [Country] =earlier([Country])), [Date],,asc,dense)
filter for 1 in visual
@amitchandak thanks for the reply. All of the data is in a single table.
Each row is a purchase, with Country, Year, Amount. It sounds like your solution assumes that year is a seperate table?
Hi @Unicorn_Tech ,
You can create the following measure:
measure = CALCULATE(MIN(Table[Year]),FILTER(Table,Table[Country]=MAX(Table[Country])))
If it doesn't work, please show us some sample data.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi. It didn't throw an error, but I don't have any values either.
Hi @Unicorn_Tech ,
Would you please show us some sample data? Then I can help you accurately.
Best Regards,
Dedmon Dai
My apologies, I redid the measure with the actual field for year. It's working great, and thank you.
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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |