Slicer behaviour in Excel (Powerpivot) vs Power BI

Anything related to PowerPivot and DAX Formuale
masterelaichi
Posts: 30
Joined: Fri Sep 02, 2016 8:36 am

Slicer behaviour in Excel (Powerpivot) vs Power BI

Postby masterelaichi » Fri Feb 09, 2018 3:03 pm

Hi,

I am trying to understand the way slicers behave in Excel and in Power BI. I have two tables- a Lookup table and a fact table. There exists a relationship between DataTable_Letter --->LookTable_letter

My question is when I add the DataTable_letter from the fact table to the Rows, and add a LookTable_letter from the LookupTable as a slicer, it has no effect on the pivot table. But when I add FactTable[Values] to the Values field, the slicer does it jobs and filters the data. This is not the case when I try to do the same thing using Power BI

However, in Power BI, when I create a table which has only the DataTable_letter and the LookTable_letter as slicer, the slicer does what it is supposed to and filters the relevant information

Slicers- Row labels.JPG
Slicer- Row Labels in excel
Slicers- Row labels.JPG (110.11 KiB) Viewed 718 times


Slicers in Power Bi

Slicer- Power BI.JPG
Slicer Power BI- row labels in Tables
Slicer- Power BI.JPG (51.26 KiB) Viewed 718 times



Is it because a table visualisation in Power BI, has an inherent filter function that is absent in a regular Excel Pivot table?

Thanks

Power BI Row Labels.pbix
Power BI file
(52.24 KiB) Downloaded 29 times

masterelaichi
Posts: 30
Joined: Fri Sep 02, 2016 8:36 am

Re: Slicer behaviour in Excel (Powerpivot) vs Power BI

Postby masterelaichi » Fri Feb 09, 2018 3:04 pm

Here is the second attachement which contains the excel file. It didn't allow me to attach 2 files for some reason
Attachments
Excel Row Labels.xlsx
(169.33 KiB) Downloaded 28 times

benholmes
Posts: 9
Joined: Tue Jan 30, 2018 9:22 pm

Re: Slicer behaviour in Excel (Powerpivot) vs Power BI

Postby benholmes » Fri Feb 09, 2018 4:21 pm

Can't comment on why there's a difference between Excel and PowerBI, as I haven't used the latter, however I believe the 'problem' in Excel is that until there's a measure placed in the PT nothingw ill be filtered (unless you physically make a filter selection on a row or column field), even if you change the slicer selections. I'd guess it's got something to do with there being nothing to calculate and result in a BLANK() result (which would then mean the row wouldn't be displayed).

As an aside, I recall a post on powerpivotpro (but can't seem to google it up) about potential problems when you use the 'wrong' side of a relationship on a row/column/slicer, ie in your case you've used the the 'many' side (from the fact table) instead of the 'one side (from the lookup table)'on the rows. it seems to work in this example because you've only got 2 tables, however if you had another fact table involved and kept the row field the same you'd run into problems. Essentially you should be using the lookup table fields not the data/fact table fields on R/C/S

masterelaichi
Posts: 30
Joined: Fri Sep 02, 2016 8:36 am

Re: Slicer behaviour in Excel (Powerpivot) vs Power BI

Postby masterelaichi » Mon Feb 12, 2018 9:48 pm

Thanks Ben, that is what I am not able to understand - why the slicers filter data in Power BI even when there is nothing added to the values field but in excel it does not unless the slicers are added from the same table

benholmes
Posts: 9
Joined: Tue Jan 30, 2018 9:22 pm

Re: Slicer behaviour in Excel (Powerpivot) vs Power BI

Postby benholmes » Tue Feb 13, 2018 5:50 am

Having thought about it a bit more, I wonder if it's due to the fact you can setup relationships as many to many in PowerBI vs only one to many in Excel? Are your PBI relationships many to many?

masterelaichi
Posts: 30
Joined: Fri Sep 02, 2016 8:36 am

Re: Slicer behaviour in Excel (Powerpivot) vs Power BI

Postby masterelaichi » Tue Feb 13, 2018 11:38 am

I created a Many-to-One relationship


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 3 guests

cron