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 %To Score ...
  • 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])) )

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))

    • Vvelarde's avatar
      Vvelarde
      Icon for Community Champion rankCommunity Champion
      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])) )