"a query on a query"

[Megr]

Dear All

I am quite new at this and hope I am in the correct forum.

Working with my data I have encountered a problem that I hope some of you can help me solve. Fingers crossed.

I am interested in looking at how many houses for elderly people a specific municipality has pr. +80 years old living in that municipality. And then compare this result with that of 5 comparable municipalities.

I have the following three tables 

- FOLK (number +80-year, name of municipality, year)

- RESO01 (name of municipality, year, number of houses, the type of houses)

- Comparison: (name of municipality, and for each municipality the 5 most comparable municipalities)

When I select a specific municipality (Municipality X) I want it to return to me the five most comparable. Based on this I want it to give the number og houses pr. +80 in each of these five municipalities. In this way, I want to make the original choice of Municipality X a source/a dependent factor for the next query. Or as the headlines state make a query on a query, if this makes sense? This must be done in a way where I can choose between different municipalities in my original choice. 

I have succeeded in making it return the 5 most comparable in the headers of a table. But when looking at the data part of the table the values returned are that of the original chosen municipality. What am I doing wrong?

In excel I would probably use a VLookup-function.

I greatly appreciate any tips - Thanks!

NB

I have attached my example it is quite messy - so I appoligize in advance 🙂

https://kl365-my.sharepoint.com/:u:/g/personal/megr_kl_dk/ETxwxLwqhpxFlhsEQfr-sAgB3EIPzN3dS_KLXq9wgr... 

[Megr]

Dear @BA_Pete 

Thank you for taking your time to answer my question.

I have tried to attach my file to the original post. It is quite messy and I think one of my problems are the way the comparison-file is structured. But I cant quite figure yout how to solve it.

[BA_Pete]

Hi @Megr ,

I've put it together as follows:

1) Created new dimMunicipality table as follows:

dimMunicipality = DISTINCT(Comparison[Name of Municipality])

2) Related dimMunicipality[Name of Muni] to RESP01[Muni]

3) Related dimMunicipality[Name of Muni] to Comparison[Name of Muni]

4) Related Comparison[Name of Muni] to FOLK1A[Muni]

5) Related dimMunicipality[Name of Muni] to FOLK1A[Muni] INACTIVE

6) Set up the report as follows:

Where:

- Slicer contains dimMunicipality[Name of Muni]. This is set to single-select to avoid any weirdness from the MANY : MANY join.

- Card contains measure showing total for primary muni as follows:

_primMunicCalc = 
CALCULATE(
    SUM('FOLK1A (+80 år)'[Befolkning]),
    USERELATIONSHIP(dimMunicipality[Name of Municipality], 'FOLK1A (+80 år)'[Municipality])
)

- Table contains FOLK1A[Muni] and FOLK1A[Befolkning]

You can add a dimYear table similar to my dim[Municipality] table and relate this to all the relevant tables to control duplication in the calculations due to this.

Pete

[Megr]

Wow you solution is looking exactly as what I want to achieve! Thank you - unfortunately I am still not succeeding. For some reason my table still is not working  and continues to give me the result of the primary Municipality.

I think it is because I still do not know how to get it to show the comparison values - the _compMunicCalc in your model. How do you calculate this and are you not using it in your table although you only mention FOLK1A[Muni] and FOLK1A[Befolkning].  Or am I totally lost?

[BA_Pete]

@Megr ,

I've not used a measure in the comparison table. As described, I've just dragged the FOLK1A[Befolkning] field into the table - no measure required.

If you really wanted a measure in the table instead of a field, it would just be:

_compMunicCalc = SUM('FOLK1A (+80 år)'[Befolkning])

but it's not strictly necessary.

Pete

[Megr]

Hi Pete

Sorry for keeping asking you, but I feel like I'm slowly going crazy. No matter what I do I simply cannot make it return the values of the comparison Municipalities in the table. I would say I'm following your instructions, but obviously I am not. 

Probably it is because I don't fully understand how your model makes the connection between the primary and that of the comparison Municipalities. 

Could I ask you to take one last look at it? It will be greatly appreciated!!

https://kl365-my.sharepoint.com/:f:/g/personal/megr_kl_dk/EoXLshcpQ4JIg4-9ITltOr4BDzQCtEr9gWgq6088PB...  

MEGR

[BA_Pete · Accepted solution]

Hi @Megr ,

There's a few small things to correct:

1) The relationship between 'comparison' and FOLK1A should be as follows:

comparison[Name of Comparison] : FOLK1A[Municipality]. I've checked my previous answer and this was my fault, I told you the wrong thing. Apologies.

2) The filter direction on the above relationship should be set to Single (comparison filters FOLK1A) like this:

3) Your table visual with the comparisons in it should use FOLK1A[Population] for the values, NOT _primMunicCalc.

4) Your slicer must contain the values from dimMunicipality. I can see from your PBIX that you've not set this up correctly. Here's how to do this:

- Go to the Data view and click 'New table'

- Enter the calculation as seen here:

Making these changes should give you the output you need, like this:

I would also recommend making your slicer single-select only. This should avoid problems arising from the Many-to-Many relationship.

Pte

[Megr]

Thank you, thank you, thank you - it finally worked!!! 

[BA_Pete]

Good news, happy to help 🙂

[v-kkf-msft]

Hi @Megr ,

Is this the output you want? Please click my PBIX file for more information. 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Megr]

Thank you for taking you time to answer my conundrum. With the help of BA_Pete I figured out the problem and got the desired result. But it seems like you also succeeded in setting the report up correctly. 

[BA_Pete]

Hi @Megr ,

This sounds like what relationships between the tables should be doing.

Difficult to say with certainty how to set it up without seeing the actual table structures, but you'd be looking along these lines:

Assuming RESO01 can be made distinct on [municipality] (probably by filtering on current year), you would relate

RESO01[municipality] ONE : MANY comparison[municipality].

Then, assuming FOLK is also distinct on municipality, relate

comparison[comparable municipality] MANY : ONE folk[municipality]. You need to then change the filter direction on this to BOTH.

You would then relate

RESO01[municipality] ONE : ONE FOLK[municipality] - this should show as an INACTIVE relationship due to your existing relationship between RESO01 > comparison > FOLK.

You would use RESO01[municipality] in any selection slicers, and use measures including USERELATIONSHIP() and SELECTEDVALUE() etc. on FOLK to calculate totals for your primary selection and comparable selections separately.

As I said, it's basically impossible for me to get this right for your specific model without seeing the data, but hopefully this gets you started.

Pete