Forum Discussion

gunicotra's avatar
gunicotra
Icon for Helper II rankHelper II
5 years ago
Solved

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?

Thanks in advance!!
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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

    • gunicotra's avatar
      gunicotra
      Icon for Helper II rankHelper II
      5 years ago

      great solution!  it works perfectly. Thanks 🙂