cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Logical measures - using in excel pivot linked to PBI dataset

I am using Excel/Inser/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.  But I am NOT using PowerBi Publisher for Excel which is not the same thing.

 

My logical measure is measure = If (A = B , 1, 0).  I am not using True/Fase 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 handly 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 inPower BI but it will not then let me use that field in excel?

 

Any advice gratefully received.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@ruthhacche
I like your logic - makes enough sense to me. Another possible explanation is that Excel does not get all the updates and functionality of Power BI (mainly so that it can maintain backwards compatibility with previous versions of Excel I think), so that accounts for many of the things that work in Power BI but not Excel.

As for using a boolean as a filter, I also probably need a night's sleep to come up with a logical explanation for this one, but it has been a problem since Power BI started. I think there's a logic if we think hard enough - if you've ever gotten the error about 'a calculate has been used in a true false expression as a filter' I think maybe these two things could be related?

You can upvote filtering on true/false here;
https://ideas.powerbi.com/ideas/idea/?ideaid=d97b8949-832c-469f-9387-5854baad0e36

In the meantime, you'll have to keep using 0, 1 or even "True" and "False" will work as Text data type.

Let me know if another night's sleep inspires some logic from you on the boolean.

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

5 REPLIES 5
Highlighted
Super User III
Super User III

Measures cannot be used as slicers and generally as filters, though they may be used for specific visuals if that measure is used in that visual. In Excel, it doesn't have filters, only slicers, so measures cannot be added to it. The reason is a Measure always returns a scalar value. In your case it is returning 1 or 0, but not 1 and 0 to be used in a slicer. If you really could add it to a slicer, you would get one of the following:

  • The result of the measure over the entire dataset, which would be one value (1 or 0) and thus the slicer is useless.
  • An error if the measure could not be used to evaluate the entire dataset.

As much as I generally dislike calculated columns, this may be a use case where they work better as you can add those to slicers in Excel. In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
SQLBI Video on Measures vs Calculated Columns




Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted

Thank you. 

Highlighted

@ruthhacche
Has this solved your question? If so, please mark the answer from @edhans as a solution so other members can find it easily and benefit from it.
Alternatively, if you try one of his suggestions, such as creating a calculated column, please post here an explanation of what you have done as a workaround and mark your own reply as a solution again so others can benefit and so the community knows this post is solved.

If it hasn't solved your question, please tell us what more you need to know or try so we can help out more.

Cheers!

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Highlighted

This answer has helped me to form my interpretation (after a night of no sleep).  The 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 measue 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 anser to the measure is until the data is already in the table.

 

A filter in excel (top left hand qaudrant of the pivot table) also works on the whole table in the same way as a slicer and will not therefore accept a measure.

 

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 total (max/ave etc) of the other lines).  So you cannot filer out some of the lines visually in a pivot table or the totals would not work.

 

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?

 

Highlighted

@ruthhacche
I like your logic - makes enough sense to me. Another possible explanation is that Excel does not get all the updates and functionality of Power BI (mainly so that it can maintain backwards compatibility with previous versions of Excel I think), so that accounts for many of the things that work in Power BI but not Excel.

As for using a boolean as a filter, I also probably need a night's sleep to come up with a logical explanation for this one, but it has been a problem since Power BI started. I think there's a logic if we think hard enough - if you've ever gotten the error about 'a calculate has been used in a true false expression as a filter' I think maybe these two things could be related?

You can upvote filtering on true/false here;
https://ideas.powerbi.com/ideas/idea/?ideaid=d97b8949-832c-469f-9387-5854baad0e36

In the meantime, you'll have to keep using 0, 1 or even "True" and "False" will work as Text data type.

Let me know if another night's sleep inspires some logic from you on the boolean.

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors