Help with a measure needed

[JennaExe79]

I've been trying to use ChatGPT to work out how to do what I'm trying to do, but have failed so far, so are hoping you can help!

I have a table in my data, called D_Forces. It has just two columns. The first is Force and there are 44 rows. The second is Region. Each Force belongs to one of 13 Regions (apart from the National Total which has no region assigned to it).

What I want to be able to do is have a single slicer on a page where the user selects the Force. Most visuals on the page will filter to show data for that single force. However, I also want some graphs (e.g. line graphs) to show the trend for all the forces within the same region.

So I need a measure which will tell the graph which forces it needs to show (the ones with the same region as the selected force on the slicer).

Can anyone suggest how to do this? The ChatGPT suggestions are either throwing up errors within the formula or are not producing the desired result.

Thsnks in advance!

[Anonymous]

Hi @JennaExe79 ,

Did Ashish_Mathur reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best regards,

Adamk Kong

[Ashish_Mathur]

Hi,

I have solved a similar question in the attached file.

Hope this helps.

[JennaExe]

Hi Ashish,

I definitely think this is very similar to what I'm trying to achieve, thank you - are you able to help me understand how to map this across to my data to work for me?

In my data, the Force name column is the equivalent of your Unique ID, because each name is different, and that is what I want to display in my slicer for people to select from. The Model column is the equivalent of my Region column. So when a user selects the Force name (unique ID), I want it to display all the Force names (Unique IDs) from the same Region (Model). I don't think I have an equivalent of your Location column, because the Force name is the Location too...

Also, I don't know if it's relevant, but I have multiple datasets in my report which all have the Force/Region columns within them, so I'm hoping the solution is one that uses my D_Forces table which is linked to all the other datasets, so I only have to set up the measures once and they can be applied to everything?

Any further help would be greatly appreciated, thank you!

[Ashish_Mathur]

Hi,

After studying my solution, please try to solve it on your own.

[JennaExe79]

Not a particularly helpful response as if I could solve it myself I wouldn't be on here asking for help!

I have managed to recreate what you've done in my own data, but I had to add an additional table for the unique ID, and add a unique ID column to my D_Forces table. 

While the table then filters just like yours does on a separate page, I don't think this solution translates into the report I've already built as none of that uses the new Unique ID table/colums I had to add to replicate your solution.

If anyone knows how I can achieve the same result using my existing tables/fields so that it works with my existing report, that would be amazing.

[DataInsights]

@JennaExe79,

Try a measure like this, where Base Measure is the underlying measure that aggregates a column:

Region Measure =
CALCULATE (
    [Base Measure],
    ALL ( D_Forces[Force] ),
    VALUES ( D_Forces[Region] )
)