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,
So, I have a power bi report where I have a slicer with values such as :
202005
202004
202003
202002
202001
Pacing
Every month, the data gets added and the category is incremented by 1. i.e in June, I'll have 202006 as well.
I have a graph where I want the data to be shown for last 3 months and Pacing by default.
i.e in May, default selection should be 202004, 202003, 202002, Pacing
in June, default selection should be 202005,202004,202003, Pacing
In other words, I want default selection of : max month-1, max month -2, max month -2 and pacing in the associated graphs.
Furthermore, I should have the flexibility to choose any month to update the view as well.
Also. I need to have all the data in the report but I need to make the graphs with a default view for few months. i.e if I have months : from 201909 to 202005. I need to show the graph for top few months but still I need to have the flexibility to update the graph for 201909. I hope this makes my question more clear?
Currently, I am manually making the selection and saving the report and then publishing it again. But to do so, I have to refresh data everytime to get updated values of the month and it has increased my file size to a great extent because the data is imported in my report. I believe that my report can crash anytime if I continue this. Hence, I need urgent help.
I am looking forward to receiving some solution for it.
Thanks,
Sunaina Khera
Solved! Go to Solution.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Test(a calculated table):
Test = DISTINCT('Table'[YearMonth])
There is no relationship between tables.
You may create a calculated column and two measures as below.
Calculated column:
YearMonth = VALUE(FORMAT('Table'[Date],"yyyymm"))
Measure:
Rank =
RANKX(
ALL('Table'),
CALCULATE(MAX('Table'[YearMonth]))
)
Visual control =
var tab =
TOPN(
3,
FILTER(
ALL('Table'),
'Table'[YearMonth]<SELECTEDVALUE(Test[YearMonth])
),
'Table'[YearMonth]
)
var _min =
MINX(
tab,
[YearMonth]
)
var _max =
MAXX(
tab,
'Table'[YearMonth]
)
return
IF(
NOT(ISFILTERED(Test[YearMonth])),
IF(
[Rank]>=1&&[Rank]<=3,
1,0
),
IF(
HASONEVALUE(Test[YearMonth]),
IF(
SELECTEDVALUE('Table'[YearMonth]) >=_min&&
SELECTEDVALUE('Table'[YearMonth]) <=_max
,
1,0
),1
)
)
Finally you may put 'Visual control' measure in the visual level filter and use 'YearMonth' column from 'Test' to filter the result.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Test(a calculated table):
Test = DISTINCT('Table'[YearMonth])
There is no relationship between tables.
You may create a calculated column and two measures as below.
Calculated column:
YearMonth = VALUE(FORMAT('Table'[Date],"yyyymm"))
Measure:
Rank =
RANKX(
ALL('Table'),
CALCULATE(MAX('Table'[YearMonth]))
)
Visual control =
var tab =
TOPN(
3,
FILTER(
ALL('Table'),
'Table'[YearMonth]<SELECTEDVALUE(Test[YearMonth])
),
'Table'[YearMonth]
)
var _min =
MINX(
tab,
[YearMonth]
)
var _max =
MAXX(
tab,
'Table'[YearMonth]
)
return
IF(
NOT(ISFILTERED(Test[YearMonth])),
IF(
[Rank]>=1&&[Rank]<=3,
1,0
),
IF(
HASONEVALUE(Test[YearMonth]),
IF(
SELECTEDVALUE('Table'[YearMonth]) >=_min&&
SELECTEDVALUE('Table'[YearMonth]) <=_max
,
1,0
),1
)
)
Finally you may put 'Visual control' measure in the visual level filter and use 'YearMonth' column from 'Test' to filter the result.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Allan,
This is exactly what I needed. Thank you so much for your help.
Regards,
Sunaina Khera
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 |
---|---|
106 | |
98 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |