Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jackiekim
Frequent Visitor

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

1 ACCEPTED SOLUTION

@jackiekim

 

just add some code in the measure:

 

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




Lima - Peru

View solution in original post

4 REPLIES 4
Vvelarde
Community Champion
Community Champion

@jackiekim

 

Hi, please try:

 

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




Lima - Peru

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?

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

@jackiekim

 

just add some code in the measure:

 

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




Lima - Peru

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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