Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ruthhacche
Helper III
Helper III

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

@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.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

5 REPLIES 5
edhans
Super User
Super User

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

Thank you. 

@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!

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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?

 

@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.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.