Hi
I have two slicers one called Organisation and the other called Division. When I filter based on Organisation the Division list does not change and shows all divisions and does not filter just the divisions that relate to the Organisation.
I have used the below measure but this does not work.
IF (
COUNTROWS ( Division ) > 0,
IF ( ISBLANK ( SUM ( Data[AUD] ) ), 0, SUM ( Data[AUD] ) )
)
I also have the same measure with USD and then use SWITCH to use a slicer to move between AUD or USD.
I have a table called Data (has columns with AUD, USD, Organisation and Division) and two other tables called Organisation and Division which I have made a relationship with Data.
I have attached a test file where you can see by clicking on Organisation the Division slicer does not change.
Thanks
Jon
Hi Jon,
Thanks for sharing a sample file.This makes it a lot easier to quickly pinpoint the problem.
Your measure is returning a zero if there is no count. The Slicer is only ignoring items with 'no data', but zero is considered 'data'.
If you write your formula: =SUM(Data[AUD]) then this results in the Slicers behaving correctly, but I suspect you wrote the formula that way to handle your FX conversion.
Mynda
Hi Mynda
Thanks for replying to my question.
The reason for the measure is so that in the Rows section of the pivot table I can show all items even if blank/zero and not just items that have a number.
I have created a P&L in a pivot table, in the Rows I have a list of all P&L accounts and then the Values the measure. So I want to show all P&L accounts no matter if they have a value or not. If I just use the SUM(Data[AUD]) it will only show the P&L accounts with a value and thus does not create the full P&L.
Thanks
Jon
Ok, that makes sense. But, that also means that your Slicer will show every item since they will contain a zero, as opposed to having 'no data'.
Mynda
Thanks so much for your help.
So I either show all P&L accounts and not be able to use both Slicers or use SUM and don't show the full list.
Yes, I'm afraid I don't know of any workaround for that with Power Pivot. With regular PivotTables there is an option to 'Show items with no data' in the field settings, but this is greyed out in Power Pivot.
Mynda
