Tag Archives: filtering

How to Solve Lookup Filtering Issue with Dynamics 365 version

The October 2017 Dynamics 365 Service Update 1 for 8.2.2 delivers a load of updates and hotfixes for Dynamics 365 Online Instances. Recently, one of our clients noticed that with this update, some of their Lookup filtering on related records was no filtering correctly. Upon investigation, this issue seems to only occur with fields that are being filtered by a lookup, which is located in the header of the form. If you are unsure of whether this will affect your system you can check your version number by clicking on the cog icon in the top right and then clicking on ‘About’.

image thumb How to Solve Lookup Filtering Issue with Dynamics 365 version

As mentioned earlier, the issue occurs when a lookup field is being filtered by another lookup field located in the header. The screenshot below shows the customization, where the lookup values of the Contact field should be filtered by the lookup value of the Account field when an Account is selected on an opportunity. Unfortunately, this filtering does not occur and the User is presented with a full list of Contacts in Dynamics 365.

image thumb 1 How to Solve Lookup Filtering Issue with Dynamics 365 version

image thumb 2 How to Solve Lookup Filtering Issue with Dynamics 365 version

A simple way to fix this would be to also put the field of the related record on the main form as a hidden field. The screenshot below shows that I have added the lookup field located in the header on the form as well and have set it to be hidden. This will allow you to keep the lookup in the header while also maintaining the filtering.

image thumb 3 How to Solve Lookup Filtering Issue with Dynamics 365 version

Let’s block ads! (Why?)

Magnetism Solutions Dynamics CRM Blog

Relevance Search – Additional Filtering Using Facets and Filters

Additional Filtering 300x225 Relevance Search – Additional Filtering Using Facets and Filters

Relevance Search distributes a search in a single result list and sorts it by relevance based on a scoring concept. One key thing to know is that the higher the score, the more relevant the item.

Relevance Search can:

• Find matches to any word in the search phrase. Matches include various forms of the search word for example, “service,” will match to “servicing,” or “serviced”

• Search for text in emails and notes

• Search records that you own as well as those that have been shared with you

• Search for text in an Option Set and Lookup field

• Search for text in SharePoint integrated documents (scheduled to be included in the next Dynamics 365 update)

• Search for text within Documents in Dynamics 365. These include documents in a Note, Attachments, Email, and Appointments.

As you can see, the Relevance Search can do many great things but it can also result in millions of matches depending on the size of your organization. Luckily, for us, Microsoft has thought about that and included a feature called Facets and Filters. We get additional filtering by Record Type, Owner, Modified Date, and Created Date to personalize search experience.

Additional Filtering using Facets and Filters

Global Facets: You can refine your search results to Record Type, Owner, Created On, or Modified On. In this example below, I filtered the search results to only show records for a specific “Owner.”

111617 2201 RelevanceSe1 Relevance Search – Additional Filtering Using Facets and Filters

Entity Specific Facets: When you click on a specific record type, additional facets appear. These facets are specific to the fields of the Record Type/Entity. System Administrators and System Customizers can configure which fields are available for faceting through the entity’s Quick Find view. In the example, clicking on Cases gave me two additional facets: Priority and Origin.

111617 2201 RelevanceSe2 Relevance Search – Additional Filtering Using Facets and Filters

End user configuration: End users can also personalize their search experience by configuring the facet fields that they would like to see for any searchable entity.

111617 2201 RelevanceSe3 Relevance Search – Additional Filtering Using Facets and Filters

111617 2201 RelevanceSe4 Relevance Search – Additional Filtering Using Facets and Filters

Now that you have learned how to narrow your search results you will get results that are more relevant to your needs, making it easier to find what you are looking for.

Keep up with the latest and greatest on Dynamics 365 by subscribing to our blog here!

Happy Dynamics 365’ing!

Let’s block ads! (Why?)

PowerObjects- Bringing Focus to Dynamics CRM

AutoExist, Cross Table Filtering, and You

There are people (at least I assume they exist) that plan out the words flying out of their mouths before they actually start speaking.  Those people lack imagination and an appreciation for the true art of throwing caution to the wind and blurting out whatever pops into your head.  It works fine for me a good 80-90% of the time!  I like to write blog posts the same way!  Even I have no idea what is about to happen, but I’m pretty excited. Also, there are people (and I know these exist – Chris Webb comes to mind) that don’t like to write blog posts against topics that have already covered. Ya, I’m not that guy either. Heck, I repeat my own posts sometimes.  In this case, the topics of AutoExist and Cross Table Filtering were understood by like 3 or 4 people about six years ago… so pretty good chance you didn’t know about these topics and if you did… you probably didn’t understand the posts anyway.  I know I didn’t. For that matter, I still don’t, but do I seem like the kind of guy that is bothered by such trivialities?  wlEmoticon openmouthedsmile AutoExist, Cross Table Filtering, and You


So, look. AutoExist has already been written about. Quite a lot. Feel free to comment with “Dude, we get it… ” and *I* promise not to write about it a 3rd time! Folks that have written about this:

All of them have a slightly different take on the concept and all are worth reading, imo.  That said… this is not something you are going to run into every week. image thumb 5 AutoExist, Cross Table Filtering, and You

My claim is that if you work long enough with DAX, you willrun into this… likely as a performance issue, but possibly as “weird results” from your measures, and then… you will be glad you at least heard about this concept.  So, store it away.  And if nothing else, it is interesting…

Let’s start by creating a simple model (at right) and one simple measure:

[Total Ticket Sales] := SUM(Sports[Ticket Sales])

image thumb 6 AutoExist, Cross Table Filtering, and YouWe have a few professional sports team, using a Cities lookup table.  I have totally totally invented some bogus Ticket Sales data.  We build a pivot table and all looks about like what we would expect (the upper pivot table, at left).

Other than some unlikely data (in the States, there is no scenario where Soccer/Futbol is going to have more ticket sales than American Football.  We really really like our football) – proof the data is fake. wlEmoticon smile AutoExist, Cross Table Filtering, and You

Okay, that was nice I guess – next step is to define a weird and mostly useless measure:

[Greeting] := “Hello”

First, let’s just recognize this is indeed a valid measure.  Measures can return text!  And hey, they don’t HAVE to do anything interesting/useful… like sum or average a column… they can just return a simple value like “Hello” or 1.23 if you want.

You probably already cheated and looked at the next pivot table, but just in case you have not… think for a moment about what you “expect” to see.  My first thought would be that the pivot table would look just like the Total Ticket Sales version… but replace all numbers with “Hello”.  Of course, I wouldn’t call it a “first” thought if that were correct.

Indeed the pivot table looks strangely “long”.  Though, at least it is friendly – look at how often it is telling us “Hello” ! wlEmoticon smile AutoExist, Cross Table Filtering, and You  Where the first pivot correctly shows only Dallas teams (Cowboys and FC Dallas), the second pivot table is showing teams from other cities.  Dallas Earthquakes is not a thing!!  image thumb 7 AutoExist, Cross Table Filtering, and You

Two questions:
1) Why does the first pivot table NOT show Earthquakes under Dallas like the second pivot table?
2) Why are some of the cells in the second pivot table BLANK?

You want the truth, you can’t handle the truth, blah, blah… a few good men.

On 1) the thing to realize is that Dallas is being evaluated against the Earthquakes – and every other sporting team.  It’s just that by default, Excel removes rows where every value is BLANK.  Our only value being [Total Ticket Sales] which doesn’t exist (eg, is BLANK) for Dallas Earthquakes… those rows are simple tossed out, showing a smaller pivot table.

On 2) is now a good time to admit that the results surprised me too? wlEmoticon smile AutoExist, Cross Table Filtering, and You

Somehow the pivot table (or… something…) knows that the Cowboys are a football team, and the Earthquakes are a soccer team, so it doesn’t bother evaluating Dallas Cowboys… the soccer team.  And it doesn’t evaluate FC Dallas the football team.  But… well, how does it know?

Btw, what is the most impressive invention of modern times?  Computers? Airplanes?  No… it’s the thermos.  It keeps hot stuff hot.  It keeps cool stuff cool.  But… how does it know!??  I’m the worst – I’m sorry.

So.  Why is it smart enough to know the Cowboys don’t play soccer, but it is not smart enough to know the Earthquakes don’t play in Dallas?

The answer is simple:  the cities are in a different table.

There is magic down in the bowels of the DAX engine that knows which combinations of column values existautomatically.  exist. automatically. autoexist. AUTOEXIST!

The “autoexist” logic magically removes combinations that don’t exist, but… only for columns in the same table.  If the columns are in different tables, all bets are off… it is going to try to evaluate every combination.  And… in the vast majority of cases you do not care.  If everything is working and performance is good – awesome!  Grab yourself the beverage of your choice, congratulate yourself on a job well done, and move along w/ your life.

However, given the knowledge of how columns from multiple tables interact… you can see how building a pivot table with lots of columns from different tables could create some performance problems.  Grab a column from a geography table of 2000 rows, one from 4000 customer table and one from 500 rows of products, and you are evaluating 2000*4000*500 combinations… and throwing away the vast majority as blanks.  Something like 3 billion combinations?  Zoinks!

The important thing here is simply awareness, but a few points:

  • If possible, avoid “detail” reports – that have lots of columns.  They make the ouchy.
  • If you need detail reports, consider using the trick from Rob’s article linked above.
  • Denormalize tables to avoid snowflake schema… as more tables simply increases the odds of running into this problem.  In our example, if we simply pulled the cities into the sport table… problem solved!  No more Dallas Earthquakes…!
  • Beware that in some interesting situations… we aren’t just talking about a performance issue, but bad results from your measure – be sure to read the post linked above at SQLBI.

Cross Table Filtering

To continue our theme of discussing things that are hard to understand, I have read this post about Cross Table Filtering more times than I can count – and each time I get some new little nugget of info and a good heap of “what in the hell is he even talking about?”  http://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html

This is a deep discussion about filter context, how it flows between tables, the impact of ALL( ) in both top and non-top level filters, and … well, just go read it.  It’s pretty intense.

How is it that I’m on my 4th year of using DAX about every day, and I still don’t understand everything in the article?  Two words:  Jeffrey. Freakin. Wang.  Jeffery is basically the father of DAX and current engineering manager overseeing the DAX engine.  I heard him speak at our local user group about a year ago and it was amazing how much Cross Table Filtering knowledge he was dumping on us.  I can’t wait to listen to him again.  I wonder when that will be…

Wait, what’s that?  He is speaking at the next Modern Excel / Power BI User Group in Seattle!?  Hurray for me!

Seriously, if you are in the area… this isn’t one to miss.

And btw… if you are in the Seattle area… I would love to meet you!  For almost 4 years, I have been running my independent consultancy at Tiny Lizard.  However, hopefully you guys saw the exciting announcement around the creation of Power Pivot Pro – Northwest, or as I like to call it P3NW.  Other than providing technical support on projects, my primary role for P3NW is in helping mentor and grow people.  People like yourselves.  I don’t care if you are seasoned or brand new – I’d love to chat about where you are in your data journey.  Drop me a line!  I’m “scott” “at” “powerpivotpro” “dot com”…

Let’s block ads! (Why?)


Filtering Errors in PowerBI

I just faced this problem when trying to analyze some information from google analytics in PowerBI. In my specific situation, I was trying to extract a number from some URL querystrings and filter the rows without the number. When I set the data type of the column to decimal, the rows without a number receive an expression error. Let’s build an example filtering errors in PowerBI.

The image below illustrate a small set of data in excel that will result in this problem. The objective is to extract the numbers and exclude all the rows without numbers. 

PBIError1 Filtering Errors in PowerBI

After importing the excel file in PowerBI, let’s extract all the content after the “=” sign using Transform => Extract=> Text After Delimiter. 

PBIError2 Filtering Errors in PowerBI

PBIError25 Filtering Errors in PowerBI

PBIError35 Filtering Errors in PowerBI

Now, let’s change the data type to decimal using Transform => Data Type

PBIError3 Filtering Errors in PowerBI

We have two errors in this resultset, as you may notice in the images below. There is no way to exclude these errors using the interface, we need to code in M to achieve this. There are two useful functions available to filter errors: Table.RemoveRowsWithErrors and Table.SelectRowsWithErrors, the names already explain what they do.

PBIError4 Filtering Errors in PowerBI

PBIError5 Filtering Errors in PowerBI

We need to open the Advanced Editor, inside View, to edit the M code.

PBIError6 Filtering Errors in PowerBI

PBIError65 Filtering Errors in PowerBI

We will add a new line before the “In” statement. In this line, we will create a new name for our result, like this:

After the “=” sign we can use the function, it has two parameters: The name of the result of the previous line and the column we want to check for errors.

Two more details to go:

  • The previous line need an extra comma at the end
  • After the “in” statement, we need to change the name of the final result set to our new name

PBIError7 Filtering Errors in PowerBI

The final result, without the errors:

PBIError8 Filtering Errors in PowerBI

Let’s block ads! (Why?)

SQL – Simple Talk

Whitepaper: Bidirectional Cross Filtering

social default image Whitepaper: Bidirectional Cross Filtering

Bidirectional cross-filtering is a new feature for SQL Server 2016 Analysis Services and Power BI Desktop that allows modelers to determine how they want filters to flow for data using relationships between tables. In SQL Server 2014, filter context of a table is based on the values in a related table. With bidirectional cross-filtering, the filter context is propagated to a second related table on the other side of a table relationship. This can help you solve the many-to-many problem without writing complicated DAX formulas.

We just released a whitepaper that describes this feature in detail, available now for download. The whitepaper covers how the feature works and how it solves problems like the traditional many-to-many scenario. It also points out other interesting use cases such as using it for a date table, and how it solves dynamic security much easier than before.

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

Filtering PartyList Fields with JavaScript: The addPreSearch Method Joins the Party

PartyList fields can be a very powerful component of activities in Dynamics CRM. Otherwise known as the Activity Party, the PartyList controls what you see in the To, From, and CC lookup fields of an activity. Sometimes you need a little more control over the UI/UX surrounding the PartyList! Currently,

Read More

 Filtering PartyList Fields with JavaScript: The addPreSearch Method Joins the Party

Let’s block ads! (Why?)

PowerObjects- Bringing Focus to Dynamics CRM

Power BI Designer tip – easy filtering of Charts – #Powerbidesigner #Powerbi

May 19, 2015 / Erik Svensen

Power BI Designer tip – easy filtering of Charts – #Powerbidesigner #Powerbi

I am working on a Power BI Dashboard analyzing the Danish population based on public data I noticed a handy way to filter your charts.

Simply drag the legend to the filter box

 Power BI Designer tip – easy filtering of Charts – #Powerbidesigner #Powerbi

You can see a video on how to do this – https://youtu.be/_cHqV7oCdZk

I will return shortly with the full dashboard on visualizing the data but here is a small teaser – I think I will start a baby toy shop in Copenhagen – Customer base is rapidly growing.

 Power BI Designer tip – easy filtering of Charts – #Powerbidesigner #Powerbi

This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

Erik Svensen