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
Anonymous
Not applicable

Measure based on one or more slicer selection

Hi guys, 

 

After being crushing my head and trying to find a solution I 've decided to ask for your help. 

 

I am trying to create a measure that changes accordingly to what is selected on the filters of the page.

 

Clarifying the situation:

 

I have one Target value by season, one by territory and one by category.  If no filter is selected it shows the Actuals Sales values vs the Total Target.

 

However, if I selected one value in my territory slicer, I would like that my measure instead of comparing Actuals Sales vs Total Target, it dynamically compares Actuals Territory vs Target Territory (based on the slicer selected) 

 

And on top of this, if I select another slicer, for example, season---  I would like that my measure now could dynamically change and compares Actuals Territory Season vs Target Territory Season. (based on the 2 selected slicers) 

 

Thanks so much in advance. 

 

Best,

Gabriel

3 REPLIES 3
PaulDBrown
Community Champion
Community Champion

@Anonymous 

 

It sounds like this could be a data structure issue. Can you share the structure of your data tables and fact tables?

(hiding any confidential data of course)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

Hi Paul,

 

Thanks for taking the time.

 

Here you have my database structure. Let me just go over a bit so you can have a clear view of it. 

 

I have one Product Categorization where I have my categories. One Date database, one Customer Master Data where I have the info of each client, then I have my Database Futures where I get my sales info. Then I have another 2 databases, Target and Fcast. Target and Fcast are connected with my CMD by client number. And every single filter & measure related to time is coming from the Dates database. 

 

I hope that can help.

Thank you. 

 

Capture.PNG

 

@Anonymous 

 

Thanks for that. It certainly helps, although it is difficult to provide useful advice without deeper understanding of the table and business structures.

The general consensus regarding best practices in data modelling is that you structure tables in two main categories: fact tables and Lookup or dimension tables, and it is highly recommended to have "single to many" relationships between these. This allows for an easier "filtering" process when it comes to measures and slicers.

 

For example, in your model, the "Dates" table and the "Target" or FCast" tables don't appear to be related, which makes it very difficult for example to compare actual sales with targets in a dynamic date period. In these case, what I do, is have a bridge "Period" table between the "Dates" table and the "Target" table for example, taking account the normally different data granularity intrinsic to each (which I cannot see in the image you have posted).

Let me put an example. Let's say your "Target" values are on a monthly granularity and your dates are on a Day granularity. I would set up a bridge period table above both these tables with a monthly granularity, including a YearMonth (integer) column common in all three tables, which I would set as the single -to -many relationship between your bridge period table to the target and dates table. This allows you to filter and create measures using this bridge period table, allowing you to have direct comparisons between sales and target data, and responsive to period slicers etc...

 

I would do the same with your country, category, client tables: create bridge tables with a common field which allows you to establish a single-to-many relationship which is what you then use for measures, slicers and visuals.

 

Here is an example:

Yearmonth.JPG

 

(Apologies because the table names are in spanish). 

Basically I have a sales data table (Campañas) which has a day level granularity, and a Target table (Objetivos equipos) which has a month level granularity. The calendar table goes down to a day level granularity but I cannot connect this calendar table to my target table due to the different granularities (in order to avoid a many-to-many relationship). I have therefore created the Yearmonth bridge table which allows me to connect both tables with a single to many relationship in both, and this table then allows me to create measures which directly filter both the forecast table and the sales table to the same period. This is the table I the use as slicer and in visuals.

 

So...after all that, can you establish bridge tables in your model joining your target and sales table through single-to-many relationships? 

 

I hope I haven't made things more confusing….





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.