cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Filter measures

Hello,

I'm relatively new to Power BI. I'm stuck with a formula for some time now so any help would be appreciated.

Is it possible to filter a measure with a number selected in a slicer and then stored in another measure?

I have two slicers: year and month, both are numbers. E.g. 2021 for year and 3 (March) for month.

I need two measures:

1 - Sum only for that year and month (3).

2- Cumulative total for that year up to month number 3.

My fact table looks something like this:

Products  Year    Month  Value

A              2021   1           10

A              2021   2           10

A              2021   3           30

A              2021   4           10

B              2021   1           20

B              2021   2           10

B              2021   3           40

B              2021   4           10

C              2021   1           15

C              2021   2           10

C              2021   3           20

C              2021   4           10

The results I expect are these:

Products   Measure 1   Measure 2

A              30                 50

B              40                 70

C              20                 45

Thanks.

Jorge

1 ACCEPTED SOLUTION
Super User

Try these 2 measures:

``````Measure 1 =
Var _Y = SELECTEDVALUE('Table'[Year])
Var _M = SELECTEDVALUE('Table'[Month])
return
CALCULATE(SUM('Table'[Value]),filter(ALLEXCEPT('Table','Table'[Products]),'Table'[Year]=_Y&&'Table'[Month]=_M))``````

``````Measure 2 =
Var _Y = SELECTEDVALUE('Table'[Year])
Var _M = SELECTEDVALUE('Table'[Month])
return
CALCULATE(SUM('Table'[Value]),filter(ALLEXCEPT('Table','Table'[Products]),'Table'[Year]=_Y&&'Table'[Month]<=_M))``````

output:

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

5 REPLIES 5
Helper I

Hello,

I'm still working on this formula and need extra help.

I have the following model:

I need the Cumulative total for the selected year/month and I'm using the formula:

AMT_BALANCE_YTD =
VAR YEAR = SELECTEDVALUE(PROFIT_CENTER[YEAR])
VAR MONTH = SELECTEDVALUE(PROFIT_CENTER[MONTH])
return
CALCULATE([Accumulated Balance];
FILTER(ALLEXCEPT(PROFIT_CENTER;DIM_STRUCTURE);PROFIT_CENTER[YEAR] = YEAR && PROFIT_CENTER[MONTH] <= MONTH))

The measure [Accumulated Balance] is: [Accumulated Balance] = SUM(PROFIT_CENTER[AMT_BALANCE])+0

What isn't working is when I select a month that doesn't have any value to a specific ID_COST_CENTER / ID_ACCOUNT, as in the example below (in green what I expect to see in a card depending on the year/month selected and in red what I'm getting with the formula I'm using.

How can I change the formula to obtain the desired results?

Jorge

Helper I

Hi @VahidDM ,

I apologize for the insistence or if I'm being intrusive, but is there any change I can make to the measure to get the desired results as I described in the previous message?

For example, in May the result should be 4306 and not zero.

Thanks.

Jorge

Super User

Try these 2 measures:

``````Measure 1 =
Var _Y = SELECTEDVALUE('Table'[Year])
Var _M = SELECTEDVALUE('Table'[Month])
return
CALCULATE(SUM('Table'[Value]),filter(ALLEXCEPT('Table','Table'[Products]),'Table'[Year]=_Y&&'Table'[Month]=_M))``````

``````Measure 2 =
Var _Y = SELECTEDVALUE('Table'[Year])
Var _M = SELECTEDVALUE('Table'[Month])
return
CALCULATE(SUM('Table'[Value]),filter(ALLEXCEPT('Table','Table'[Products]),'Table'[Year]=_Y&&'Table'[Month]<=_M))``````

output:

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

Helper I

Hi @VahidDM,

Thanks. It almost works. And that's because I've simplified one thing that I thought it wouldn't affect.

I have a dimension table to Products and to the resulting table I'm using the field from DIM_PRODUCTS. I need this, because there's a third table with the expected quantity for products and it could exist Porduct D on that table and not on the first table.

So the exact model is:

[TABLE A]

Products  Year    Month  Value

A              2021   1           10

A              2021   2           10

A              2021   3           30

A              2021   4           10

B              2021   1           20

B              2021   2           10

B              2021   3           40

B              2021   4           10

C              2021   1           15

C              2021   2           10

C              2021   3           20

C              2021   4           10

[TABLE B]

Products  Year    Month  Value

A              2021   1           10

A              2021   2           10

A              2021   3           30

A              2021   4           10

B              2021   1           20

B              2021   2           10

B              2021   3           40

B              2021   4           10

C              2021   1           15

C              2021   2           10

C              2021   3           20

C              2021   4           10

D              2021   1           15

D              2021   2           10

D              2021   3           20

D              2021   4           10

[DIM_PRODUCTS]

Products Value

A             5

B             5

C             5

D             5

Table A and Table B are both related to DIM_PRODUCTS.

Helper I

In the results table I will also have two measures using Table B, identical to the other two measures, so I would expect something like this:

The results I expect are these:

Products   Measure 1   Measure 2   Measure 3   Measure 4

A              30                 50               30                 50

B              40                 70               40                 70

C              20                 45               20                 45

D              0                   0                 20                 45

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors