cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jackiekim Frequent Visitor
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

Accepted Solutions
Vvelarde Super Contributor
Super Contributor

Re: Filter measure with seperate table

@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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
Vvelarde Super Contributor
Super Contributor

Re: Filter measure with seperate table

@jackiekim

 

Hi, please try:

 

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




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




jackiekim Frequent Visitor
Frequent Visitor

Re: Filter measure with seperate table

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 Frequent Visitor
Frequent Visitor

Re: Filter measure with seperate table

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 Super Contributor
Super Contributor

Re: Filter measure with seperate table

@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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 285 members 2,799 guests
Please welcome our newest community members: