Selection from slicer compared to another column

[Anonymous]

Hi, I've been working very hard trying to fix one problem in a report. Basically what's going on is that I have a slicer with a couple options and I can't get it to filter my table. The slicer is from a previous page of the report. It comes from Query 1 but the table is Query 3. I needed to make a relationship between Query 1 and 3 for a different slicer and table and variable so it is not an option to make a relationship although I know that would work (but it would mess up the other slicer's filtering). I've read some other ideas about merging and making a new table and stuff but it all confused me. I'm working with 4 queries in this report all with slightly different columns and some overlap between a few of them so I can't really figure out how to work around all of it.

The slicer is for "Pool Name" which is a column in both Query 1 and 3 and is text. I'm trying to make another column or measure that can take the value selected from the slicer and use it to filter the table. So I created . This works correctly, it returns the value that is selected. What I want now is a column that is IF(Query3[Measure]=Query3[NAME3], "True", "False") or using EXACT() or CONTAINSSTRING() all doing the same thing of comparing the values. None of these worked for me (and using LEN() didn't either) because all of these were comparing Query3{NAME3] with the else value of the Measure. I originally had an else value of "ALL", and so the len was 3 no matter what was actually selected on the slicer.

I'm hoping for a way to compare the return value of SELECTEDVALUE with another column. That's my ideal solution right now but if this seems unlikely, I'm open to other suggestions. As with most things in life I'm sure there are multiple ways to fix my actual problem which relates back to the different queries and relationships.

Thanks in advance,

Michelle

[d_gosbell]

If create a column using a reference to a measure like the one you have created it will not work the way you expect because columns are only re-calculated during a data refresh, they will not change in response to slicer selections. But you can still create a measure that would have a similar effect. 

So if you had an [Amount] column in Query3 and wanted to filter it based on the [Name] column from Query1 you could write a measure like the following

Query3 Amount = CALCULATE( 
    SUM( Query3[Amount] ), 
    TREATAS( VALUES( Query1[Name] ) , Query3[Name] )
)

This will take the selected values from Query1[Name]  and treat them as if they were selected from the name column in Query3.

However if I'm reading between the lines here possibly a better solution requiring no tricky DAX formulas would be to create a new 'Pool' table that has a distinct list of all possible pool name values, then create a relationship between this table and both Query1 and Query3, then you would hide the [Name] column in Query1 and Query3 and only use the one from the 'Pool' table. 

[Anonymous]

I tried your last suggestion because it seemed so simple. Almost worked but I get this:

Am I making the relationships wrong? 

[d_gosbell]

---

@Anonymous wrote:

Am I making the relationships wrong? 

---

No, this would be the new relationship conflicting with one of the existing relationships in your model. Like I said, I was reading between the lines trying to guess what your model looked like. 

The options from here are either to use the TREATAS style of expression or possibly look at pulling out the other column that is currently joining Query1 and Query3 into it's own table. But it's tricky to deal with this sort of thing in a forum setting as we'd really need a deeper understanding of the business context to try and figure out if it makes sense to re-structure the model like this.

If Query1 was a Sales table and Query3 was an Inventory table and they were currently joined on a Product column then I would say that "Product" is a concept in it's own right and should be in it's own table. 

[MFelix]

Hi @Anonymous ,

Looking at what you have writen I'm assuming that you can create a relationship between both tables although is not the preferable scenario for your calculations due to ohter fields.

One option is to create an inactive relationship between both tables and then create the measures for calculation with the USERELATIONSHIP function, that basically activates inactive relations uppon calculation of measures, that way you can use the same slice for bot pages.

But your information is very reduced cand you share a sample of your data and expected result?

Regards,

MFelix

[Anonymous]

Hi,

Here is my current table: 

Sorry about the lines, I can't show any numbers because of privacy. My slicer for this has two options: Basic Refinery and Additives. So I want this table to only display Additives rows when that is selected on the slicer. 

Query 1 contains different numeric fields and it contains CODE which is the pool code (I could be filtering by this but I don' see why this would be any different because it's the same thing but numeric) and NAME3 which is the pool name. Query 1 also contains COUNTRY which I am also filtering by. This is a slicer on the first page that is then filtering the entire report to use that country. This is the active relationship variable. I have a table on a different page that is where the slicer for pool name is first used and that table is made up of Query 1 data. So in the end I have two different tables being (hopefully) filtered by pool name, one for query 1 data and one for query 3 data.

Query 3 has fields like those in that table above. It also had CODE and NAME for the pools. It also has COUNTRY which is actively related many to many with Query 1 COUNTRY. If I get rid of this relationship the table starts showing data for every country. 

The other two queries (2 and 4) don't have the pool code or name. They also have country and need to be filtered by that on their respective pages but I think that is working fine so far. 

There are 9 different pools. There are also 9 different countries.

I don't know if this is enough information or changes your suggestion. I'm trying to figure out the whole UseRelationship thing but I'm confused on what it actually does. Not sure if every column of that table needs to be constructed to use calculate and userelationship. 

Every work around I try to do with the relationships fails because it creates ambiguity between queries. 

Thanks,

Michelle

[d_gosbell · Accepted solution]

Hi Michelle,

That description of your issue does help and based on that I would suggest setting up your model as follows. Removing the existing relationships and adding another new table with a distinct list of countries.

 

With your 4 existing tables in the middle row, with our new "Pool" table at the bottom and a new Country table at the top. Then I would hide the pool and country fields in the existing tables so that you don't accidentally use those.

You will have to replace the Country column in any existing reports with the one from this new table, but it should make any calculations you need to do much simpler. And you can repeat this pattern multiple time (eg, adding more tables like Employees, Products, etc). 

This sort of approach is called Dimensional Modelling (first developed by a person called Ralph Kimball who wrote a number of books on the subject) and it works really well with Power BI. 

[Anonymous]

Thank you this worked with the new tables for Country and Pool. Thanks for all of your help! Have a great day!