Forum Discussion

jorge_oliveira's avatar
jorge_oliveira
Helper I
4 years ago
Solved

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...
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi jorge_oliveira 

     

    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.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

VahidDM's avatar
VahidDM
Super User
4 years ago

Hi jorge_oliveira 

 

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.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

 

 

  • jorge_oliveira's avatar
    jorge_oliveira
    Helper I
    4 years ago

    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.

    • jorge_oliveira's avatar
      jorge_oliveira
      Helper I
      4 years ago

      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