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'd like to know if it's possible to filter a month and preserve the values in a chart.
I have the following sales data:
When I click on the month filter, I would like it to be displayed month by month until the selected month and the rest of the months appear blank.
Would it be possible to make this chart ?
Thanks a lot.
Solved! Go to Solution.
Hey,
this Power BI report contains a report page "Date Axis unrelated", that demonstrates how a slicer can be used to limit the value used on an axis.
Basically it's not the default, slicing (selecting) means filtering - that's the magic of Power BI.
To change this behavior, I use an anrelated dimension, in this example it's either related to fact tables nor to calendar table.
The simple thing
Be aware that I'm using a column from the unrelated table.
Now it's necessary to "map" the data to the colums from the unrelated table, this mapping is necessary for each measure that you want to use with the unrelated column.
For this example this is done by this measure:
show MonthValues Unrelated = var maxDateSelection = MAXX(ALLSELECTED('Calendar'),MAX('Calendar'[MonthNoIndex])) return // lesser than means values are shown as column IF(MAX('Calendar Unrelated'[MonthNoIndex]) <= maxDateSelection, //map the values to the columns of the //unrelated table CALCULATE( SUM('FactWithDates'[Amount]), //using the axis value from the unrelated table to map the facts to the // unrelated table //calendar unrelated -> calendar -> facts FILTER(All('Calendar'), 'Calendar'[MonthNoIndex] = max('Calendar Unrelated'[MonthNoIndex]) && 'Calendar'[Year] = max('Calendar'[Year]) ) ) )
Hope this helps
Hey,
this Power BI report contains a report page "Date Axis unrelated", that demonstrates how a slicer can be used to limit the value used on an axis.
Basically it's not the default, slicing (selecting) means filtering - that's the magic of Power BI.
To change this behavior, I use an anrelated dimension, in this example it's either related to fact tables nor to calendar table.
The simple thing
Be aware that I'm using a column from the unrelated table.
Now it's necessary to "map" the data to the colums from the unrelated table, this mapping is necessary for each measure that you want to use with the unrelated column.
For this example this is done by this measure:
show MonthValues Unrelated = var maxDateSelection = MAXX(ALLSELECTED('Calendar'),MAX('Calendar'[MonthNoIndex])) return // lesser than means values are shown as column IF(MAX('Calendar Unrelated'[MonthNoIndex]) <= maxDateSelection, //map the values to the columns of the //unrelated table CALCULATE( SUM('FactWithDates'[Amount]), //using the axis value from the unrelated table to map the facts to the // unrelated table //calendar unrelated -> calendar -> facts FILTER(All('Calendar'), 'Calendar'[MonthNoIndex] = max('Calendar Unrelated'[MonthNoIndex]) && 'Calendar'[Year] = max('Calendar'[Year]) ) ) )
Hope this helps
Hi @TomMartens ,
Thanks for posting this data file. It's super helpful. I followed your guides but now am having another problem with column total is not displaying. I presented the data in a Matrix table and it shows nothing on the Column total
Any ideas?
thanks
Hi @TomMartens, I do not know how to express my gratitute towards you for your help. I will never forget your help.
Thanks a lot for your help.
You're welcome! I'm glad I was able to help!
It was a pleasure to think about your question and put it into my growing (hopefully) pbix file.
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 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |