Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have create a matrix with two columns, the column names are "year" and "visitor". "Visitor" is actually a measure filtered by a date type measure "Year". The table structure is like this:
Year | Visitor |
2017 | 260 |
2016 | 220 |
2015 | 230 |
2014 | 210 |
What I would like to do is to add another column into this matrix. The third column is named "MaxVisitor". I would like to have this column to always display the Maximum value of the "Visitor" measure, the way I would like it to display is like this:
Year | Visitor | MaxVisitor |
2017 | 240 | 260 |
2016 | 260 | 260 |
2015 | 230 | 260 |
2014 | 210 | 260 |
as you can see that the third column "MaxVisitor" always displays 260 which is the largest value from the column (i.e the filtered measure) "Visitor".
I am not sure if this can be done?
Any advice will be appreciated!
Solved! Go to Solution.
Hi @skytulip33
This measure should work for you where you table is called "Table3"
Max Visitor = CALCULATE(MAX('Table3'[Visitor]),ALL('Table3'))
What if Visitor is a measure created and not a loaded column, you cant use MAX fucntion as it wil give you error: could not be found
I tried using MAXX but still have issues
please guide
Thanks
Abhilash.
Hi @GilbertQ,
Thanks for your reply. I tried your solution but it seemed that it doesn't work.
The value calculcated from your formula seems to be the SUM of all visitors rather than the Max visitors filtered by the Year? It seemed that the "year" filter is ignored by that formula?
Hi @skytulip33
Please see the image below where I have taken your data and put it with the measure into Power BI.
Thank you for that. I tried again and realized that I missed sth. in my description. I didn't realize that it would cause this difference before.
I need to modify my description, that is, what if the customers are divided into two goups? say the original data is like:
Year | Visitor | Customer Group |
2017 | 260 | 1 |
2016 | 220 | 1 |
2015 | 230 | 1 |
2014 | 210 | 1 |
2017 | 265 | 2 |
2016 | 262 | 2 |
2015 | 158 | 2 |
2014 | 222 | 2 |
and when I tried to use your solution, it seemed that the MaxVisitor will be incorrect for Group 1. As can be seen in the pic:
Do you mind giving some suggestion on this?
Thank you!
Hi @skytulip33
What if you had to try this measure.
Max Visitor = CALCULATE(MAX('Table3'[Visitor]),ALLSELECTED('Table3'[Customer Group]))
Thanks for your reply. I did a bit of test and modified a bit on your suggested solution, the following one works fine:
MaxVisitor = calculate(max(Table3[Visitor]),ALLSELECTED(Table3[Year]))
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
86 | |
85 | |
67 | |
49 |
User | Count |
---|---|
134 | |
113 | |
100 | |
68 | |
67 |