topic Re: Filtering a table using measures based on slicers in Desktop

Filtering a table using measures based on slicers

daniel_baciu — Fri, 14 Jul 2023 16:14:28 GMT

Hi,

This is a very easy question for the experienced users.

I have a table:

I want to show a visual (table/matrix) showing only the changes by project from a scenario to another. For example - result of changes in JUL scenario vs APR scenario should look like below, as there is only one change in project A between the 2 scenarios in the same period:

Currently, this is managed to be calculated using 2 manual measures where i filtered APR (see below) and JUL separately

APR = CALCULATE( SUM(Data[Value]), Filter( 'data', 'data'[Scenario] = "APR Scenario" ) )

and another measure ("Changes") where i deducted APR measure result from JUL measure:

Changes = [JUL] - [APR]

Question is - how can i automatically calculate the Changes by asking the user to select in a slicer the first scenario and the second scenario (like below)?

User should select in the first one JUL and in the second one APR, and the result should appear in a table/matrix visual like below:

See here the pbix.

Thanks!

Re: Filtering a table using measures based on slicers

Idrissshatila — Fri, 14 Jul 2023 20:52:26 GMT

Hello daniel_baciu ,

Try using field parameters, check it out here https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

Re: Filtering a table using measures based on slicers

v-zhangti — Thu, 20 Jul 2023 07:42:00 GMT

Hi, daniel_baciu

You can try the following methods.

Change = IF(HASONEVALUE(Data[Scenario]),SUM(Data[Value]),CALCULATE(MAX(Data[Value])-MIN(Data[Value]),ALLSELECTED(Data[Scenario])))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

Re: Filtering a table using measures based on slicers

daniel_baciu — Thu, 20 Jul 2023 15:54:00 GMT

Many Thanks!
This works fine but, i didn't realize before one additional complexity. Your solutions works fine when there are the same number & name of projects in every scenario (project A, B, C appear in every scenario).

Unfortunately your solution doesn't work when projects disapear or new projects appear in future scenarios... I reattached your solution and have updated OCT Scenario (removed project C and added Project D).

Solution of the Change should be as follows:

Project Change

A 100

B 0

C -50

D 60

May i kindly ask you to have another look to see if you can provide an updated solution to this specific issue?

Many thanks!

File is here.