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.
Hi I have a table visual which displays Month - Year (mmm-dd) and average payload.
| Month | Payload |
| m - 12 | x |
|. |
|. |
|. |
|. |
|selected Month by slicer | x for that month <-------
I have two slicers, Month and Year which are from my date dimension.
I have a calculated Rank Column in my Date Dimension table:
MTC Rank = RANKX('Working Date','Working Date'[FullDate],,asc).
On my table visual I have used the visual level feature saying top 11 month sorted by rank month..
Without selecting anything from my slicers, I get the the desired out come. My issue is that when I make a slicer selection I either only see dates, for that said month or year. I am aware that my Rank function does not take into account slicer context but I am not sure how to make it behave in that manner.
The selected Month/Year should appear at the bottom of my table visual and above it I should see the previous 11 months.
E.g If I select October 2017
starting from the top of the table to the bottom, I should see November 2016, December 2016 ....... October 2017.
I have been through numerous posts on the forum and the closest I have found to what I need can be found here:
Thanks in advance for anyone who might be able to help or point me in the right direction
Solved! Go to Solution.
@D1ltang wrote:
Hi @Eric_Zhang,
First of all thank you for taking the time and effort to reply. From your demo I can see that you have approached this the same way I have. What I am not sure how to do and have tested this against your dashboard is:
If you select Year: 2017, Month January and TopN as 2.
What I need to see in my visual is :
|| MonthKey || Measure ||
|| 01/12/2016 || someValue ||
|| 01/01/2017 || someValue ||
Do you have any ideas as to how your example can be modified to acheive this?
Thanks,
Dil
It shall work. The measure is like
Measure = IF ( AND ( MAX ( yourTable[Month Key] ) <= MAX ( 'dim date'[Month Key] ), MAX ( yourTable[Month Key] ) >= DATE ( YEAR ( MAX ( 'dim date'[Month Key] ) ), MONTH ( MAX ( 'dim date'[Month Key] ) ) - MAX ( 'TopN'[N] )+1, 1 ) ), SUM ( yourTable[value] ), BLANK () )
Hi I have a table visual which displays Month - Year (mmm-dd) and average payload.
| Month | Payload |
| m - 12 | x |
|. |
|. |
|. |
|. |
|selected Month by slicer | x for that month <-------
I have two slicers, Month and Year which are from my date dimension.
I have a calculated Rank Column in my Date Dimension table:
MTC Rank = RANKX('Working Date','Working Date'[FullDate],,asc).
On my table visual I have used the visual level feature saying top 11 month sorted by rank month..
Without selecting anything from my slicers, I get the the desired out come. My issue is that when I make a slicer selection I either only see dates, for that said month or year. I am aware that my Rank function does not take into account slicer context but I am not sure how to make it behave in that manner.
The selected Month/Year should appear at the bottom of my table visual and above it I should see the previous 11 months.
E.g If I select October 2017
starting from the top of the table to the bottom, I should see November 2016, December 2016 ....... October 2017.
I have been through numerous posts on the forum and the closest I have found to what I need can be found here:
Thanks in advance for anyone who might be able to help or point me in the right direction
Hi @Eric_Zhang,
First of all thank you for taking the time and effort to reply. From your demo I can see that you have approached this the same way I have. What I am not sure how to do and have tested this against your dashboard is:
If you select Year: 2017, Month January and TopN as 2.
What I need to see in my visual is :
|| MonthKey || Measure ||
|| 01/12/2016 || someValue ||
|| 01/01/2017 || someValue ||
Do you have any ideas as to how your example can be modified to acheive this?
Thanks,
Dil
@D1ltang wrote:
Hi @Eric_Zhang,
First of all thank you for taking the time and effort to reply. From your demo I can see that you have approached this the same way I have. What I am not sure how to do and have tested this against your dashboard is:
If you select Year: 2017, Month January and TopN as 2.
What I need to see in my visual is :
|| MonthKey || Measure ||
|| 01/12/2016 || someValue ||
|| 01/01/2017 || someValue ||
Do you have any ideas as to how your example can be modified to acheive this?
Thanks,
Dil
It shall work. The measure is like
Measure = IF ( AND ( MAX ( yourTable[Month Key] ) <= MAX ( 'dim date'[Month Key] ), MAX ( yourTable[Month Key] ) >= DATE ( YEAR ( MAX ( 'dim date'[Month Key] ) ), MONTH ( MAX ( 'dim date'[Month Key] ) ) - MAX ( 'TopN'[N] )+1, 1 ) ), SUM ( yourTable[value] ), BLANK () )
Could it be possible to do the same thing but with the years? in a report I'm currently working on, I need to select a year to present only one year on multiple visual, but for few visual I would like to presente the previous year in the format:
If I select 2018 on my date slicer
Year | Sales
2018 | ....
2017 | ....
and if I select 2017 on my slicer it would become
Year | Sales
2017 | ....
2016 | ....
My slicer is on my date table which is link to my sales table. So when I've tried to tweak your formula it still only show me the year on my slicer.
Thx.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |