Excel Power BI dataset connection - power pivot fitlers

[mariodx]

Hi,

When I connect to a PBI dataset published on Power BI Service to analyse data in Excel and use filters to filter a specific table, it so happens that if the selection in the filter is "All" Excel does not recognise the selection I make, but rather it treats it as the default "All" of the filters, meaning it selects all values in the filter.

My filter contains values {"Include", "Exclude", "All"}. The options are used for partioning and selecting all options necessarily creeates duplicates as "All" is the union of the data labelled as "include" or "exclude".

It is odd that the filter is senstivie to the string value by which the dataset is filtered, but it appears to me that this is the case. Is anyone familiar with this? Do you have any idea if Excel treats "All" as all values selected in the filter? Renaming the options certainly resolves it but I am curious to find out why this happens.

I prepared a sample file table to illustrate the issue.

Sample = 
VAR partition = 
    SELECTCOLUMNS (
    { "All", "incl", "excl" } ,
    "partition_filter" , [Value] 
    )
VAR integers = 
SELECTCOLUMNS ( 
    { 1 } , "Number", [Value]
)
VAR letters = 
SELECTCOLUMNS ( 
    { "A", "B", "C","D", "E"} , "Letter", [Value]
)
RETURN
CROSSJOIN ( partition, integers , letters )

-- Measure
NumSum = SUM (Sample[Number])

Notice that when I connect to this report, when I select "All" in the dropdown menu of the filter It just gets into an infinite self-loop and I can never select "All" and filter the table.  This infinite loop is not the case in the other report so we could not immediately see where the problem was, but this sample shows how Excel behaves when a certain value is selected to filter the data. Obviously, never use "All" if any of your analysts will use Excel to do their own analysis.

If I slightly modify the value to "_All" then the problem disappers and everything works as it should.

Best,

[v-xiaotang]

Hi @mariodx 

Thanks for reaching out to us.

Does this measure works fine in Power BI Desktop but fails in Excel?

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[mariodx]

Hi @v-xiaotang ,

The measure works fine in both Power BI and Excel.

The issue comes from the value "All" in the partition, column which is a slicer in the Power BI report. The same column is used as a filter in Excel. As you can see from the images above, Excel does not treat "All" as a value of the column partition but as the default option of the Power Pivot tables "All". This results in a endless self-reference to "All" and whenever you select the value and click OK to filter the data, it does not do it and keeps the default "Select All" option. There is no such problem in Power BI. But is seems Excel works differently. To me, though, it seems as a bug. 

Best,