Forum Discussion

jackiekim's avatar
jackiekim
Frequent Visitor
9 years ago
Solved

Filter measure with seperate table

Hi, I am new to PowerBI,

 

I have a measure looks like : %Year = DIVIDE(SUM(Shipment[Profit]),SUM(Shipment[Revenue]))

 

 And I have Score Table which is separated

ScoreID%From%ToScore
1051
25152
315253
425454
545605
6601006

 

I would like to make a measure called Score and show Score[Score] filtered by %From <= %Year < %To 

ex) Score = Calculate(Score[Score], Filter(Score[%From] <= Shipment[%Year] < Score[%To] 

 

Thank you

  • Vvelarde's avatar
    Vvelarde
    9 years ago

    jackiekim

     

    just add some code in the measure:

     

    ScoreM = IFHASONEVALUE(TABLE[YOURCOLUMN]),Calculate(Values(Score[Score]), Filter(Score, Score[%From] >= [%Year]  && Score[%To] < [%Year])) )

4 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    jackiekim

     

    Hi, please try:

     

    ScoreM = Calculate(Values(Score[Score]), Filter(Score, Score[%From] >= [%Year]  && Score[%To] < [%Year])) 

    • jackiekim's avatar
      jackiekim
      Frequent Visitor
      9 years ago

      Hi, Thanks for the reply,

       

      I tried with your suggestion, 

       

      I got error 

       

      Couldn't load the data for this visual

       

      MdxScript(Model) (14,42) Calculation error in measure 'Shipments'[ScoreM]: A table of multiple values was supplied where a single value was expected.

       

      Could you help me more?

      • jackiekim's avatar
        jackiekim
        Frequent Visitor
        9 years ago

        If I hard code the value [%Year], it's working 

         

        ex) ScoreM = CALCULATE(VALUES(Score[Score]),FILTER(Score,Score[%From]<=4.5 && Score[%To]>=4.5))