Excel pivot based on PBIdataset - logical measures

Anything related to PowerPivot and DAX Formuale
Posts: 5
Joined: Thu Apr 09, 2020 5:36 pm

Excel pivot based on PBIdataset - logical measures

Postby RuthBallinger » Tue Sep 29, 2020 1:04 am

I am using Excel/Insert/PivotTable/From PowerBI (create a PivotTable connected to a PowerBI dataset).  This is PowerBI pro and I am not sure if the feature has yet been rolled out fully. 

My logical measure is measure = If (A = B , 1, 0).  I am not using True/False because by using 1/0 I can filter my visual in Power BI to only be >0 (ie to only include true answers).  But it will at least filter that way.

When I pivot this dataset in excel it will only let me add this measure to the values - not to the filters.  So I have to use a manual filter on the values which is not good enough.

a) I do not understand why PowerBI cannot handle True/false in filters for visuals - maybe if I did I would understand better what is happening in excel

b) How come I can filter using logical (1,0) measure in Power BI but it will not then let me use that field in excel?

Any advice gratefully received.

Posts: 5
Joined: Thu Apr 09, 2020 5:36 pm

Re: Excel pivot based on PBIdataset - logical measures

Postby RuthBallinger » Tue Sep 29, 2020 5:09 pm

After a night of no sleep trying to rationalise this - the first issue really has nothing to do with whether this is a logical - it is simply to do with it being a measure.  This is how my brain is rationalising it:

A slicer (in both excel and PBI) works on the pivot/visual as a whole and therefore filters what data makes it into the pivot/visual.  Since a measure is dynamic and works on each line in the pivot/visual, a measure cannot be used in a slicer because we do not know what the answer to the measure is until the data is already in the table.

A filter in excel (top left hand quadrant of the pivot table) also works on the whole table in the same way as a slicer and will not therefore accept a measure (to be fair I have never really understood why you need slicers in excel when you already have those filters).

However (and I am not sure if this is true but it works in my brain) a filter on a visual is not filtering what goes into the visual - it is simply filtering what is displayed in the visual - and it can do this because in a visual totals are calculated as independent results distinct from all the other lines in the visual (whereas totals in a  pivot table are the total (max/count etc) of the other lines - this is why new PBI users get so confused when totals work in a pivot in excel but do not work in their visual if they have not applied their DAX correctly).  So you cannot filer out some of the lines visually in a pivot table or the totals would not work.

This makes sense when you consider that the filter on a pivot can be any field whereas the filters on a visual can only be fields that are included in the visual.

So that leaves me with the other part of my question that does relate to logicals.  How come I can add a logical measure with a 1,0 answer to a filter on a visual whereas I cannot add a logical measure with a true/false answer?

If anyone has anything to add to the first part then please do. (And should excel be limiting the filters on the pivot to behave like those on a visual - ie restricting to fields actually in the pivot but allowing measures - leaving other fields to only work as filters in slicers?) And if anyone can explain the second part I really need to understand it.

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest