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 II

## slicer selected choice

Hi to all,

I am trying to change dynamically a measure coherently with the value of a year slicer, but this is not working properly.

I have a:

- Year slicer with the column of a Table1 "Table1[Year]" which may have two years 2020, 2021

- measure on a Table2 which should be dependent on that slicer. I have tryed with three different solutions:

Sol1: This is working.... but it is not dynamically dependent on the slicer!
CALCULATE(SUM(Table2[Counter]),
Table2[CODE]="10"||Table2[CODE]="11"||Table2[CODE]="12"||Table2[CODE]="13"||Table2[CODE]="14",
'Table2'[ANNO_CAMPAGNA] = 2020),

Sol2: This is not working. Even if the selectedvalue(Table1[YEAR]) is correctly 2020, the result I got, is all the data of 2020 and 2021,
If(SELECTEDVALUE(Table1[YEAR])=2020,
CALCULATE(SUM(Table2[Counter]),
Table2[CODE]="10"||Table2[CODE]="11"||Table2[CODE]="12"||Table2[CODE]="13"||Table2[CODE]="14",
'Table2'[YEAR] = 2020),

CALCULATE(SUM(Table2[Counter]),
Table2[CODE]="10"||Table2[CODE]="11"||Table2[CODE]="12"||Table2[CODE]="13"||Table2[CODE]="14",
'Table2'[YEAR] = 2021),

Sol3: This is partially working. For some reason it gives me "some" data of 2020 (not all of them!) and none of those of 2021.
CALCULATE(SUM(Table2[Counter]),
Table2[CODE]="10"||Table2[CODE]="11"||Table2[CODE]="12"||Table2[CODE]="13"||Table2[CODE]="14",
FILTER(Table2, Table2'[YEAR] IN VALUES Table1[YEAR]),

Any hints?

1 ACCEPTED SOLUTION
Super User

@gunicotra , This kind of measure is suitable if table 1 and table 2 are not connected

measure =
var _year = allselected(Table[Year])
return
CALCULATE(SUM(Table2[Counter]),filter(Table2 , Table2[CODE] in {"10", "11", "12","13","14"} && 'Table2'[YEAR] in _year))

because if they are connected , it will already have filter impact

I explained it here

Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE

3 REPLIES 3
Super User

@gunicotra , This kind of measure is suitable if table 1 and table 2 are not connected

measure =
var _year = allselected(Table[Year])
return
CALCULATE(SUM(Table2[Counter]),filter(Table2 , Table2[CODE] in {"10", "11", "12","13","14"} && 'Table2'[YEAR] in _year))

because if they are connected , it will already have filter impact

I explained it here

Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE

Helper II

great solution!  it works perfectly. Thanks 🙂

Helper II

N.B.: "ANNO_CAMPAGNA" means "YEAR"

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.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors