Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
CasperSeve
Frequent Visitor

Multiple slicers affecting measures and visual

Hi folks,

I'm pretty new to Power BI, and currently I am doing long term capacity planning for my job.

Today everything is done via Excel and that's does the trick for the most part. However, having the information in Power BI being able to select forecasts and short term finance targets, quickly makes Excel inferior.

I'm kinda puzzled on how to solve this - So hope someone can help me out here:

My objective is to be able to select, on the fly, various forecasts, and compare to my total yearly supply, hold up against my maximum network utilisation rate, to ultimately calculate my required additional capacity.

 

I have a database with a 10-year forecasts and a column with a unique forecast name. I need the user to be able to select only one here which is used for calculations. Let's call this primForecast. The forecasts are at least updated every year, with occasional additional updates coming throughout the year. It starts with current year + 10 years ahead. E.g. 2022 to 2031, next year it will be 2023 to 2031 etc.

 

Furthermore, I have a table with short term financial target settings, where I need the user to select one if there is a target available. Otherwise selection is just none (N/A). This will be used for calculations as well. Let's call this targetForecast. The target forecasts are typically 3-year targets. E.g. from 2022 to 2025, and next year it will be from 2023 to 2026 etc.

 

Lastly, I want the user to be able to display one or more of the 10-year forecasts on the visualisation, but only as line visuals without being used as calculations forecasts. Naturally, if this is feasible, the user should not be able to select the same forecast as the one being selected in primForecast. Potentially, this slicer would be reset everytime a primForecast is altered.

 

Let's make an example of how the table would like like (Excel format):

 

Example A.png

If a targetForecast i selected, and it has values, those values for those specific years, are used within the measure to calculate my additional required capacity. Otherwise if there is no values for the given year, the selected primForecast is used.

To calculate my additional required supply that is done like this:

 

(demand / maximum utilisation rate (%)) - total supply

If the additional required supply is negative (so not needed), I simply set it to a N/A value.

 

As mentioned above, I need a slicer where the user can select additional forecasts to be showcased on the visualisation, but not used as a calculation for the required capacity - and it cannot be the same as the primForecast selected.

 

An example of the below:

 

Example B.png

 

So what would be the smartest setup to do this within PowerBI and how to start? I suppose I need to make some measures that can handle this conditionality in the slicers, and furthermore to calculate the additional required supply?

 

I'm thinking about setting all of this up within a Datamart, so that my users do not need to setup these measures in their reports themselves, but this is a part of the datamodel in the dataset.

 

Thanks in advance for any guidance!

1 REPLY 1
v-shex-msft
Community Support
Community Support

Hi @CasperSeve,

Did these records really include in your table? If that is the case, you can use calculation expression can extract the current date as a variable to filter and calculate the corresponding date ranges.

You can use the aggregate function(min, max) to get the current date, then use the if statement to check the start end date range based on the current date.

Notice: if you want to use a slicer to achieve the selector effect instead of the filter, you need to use a disconnected table as the source of the slicer.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.