cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dfsadvantage
Frequent Visitor

If Conditions in a measure

Need assistance with creating a calculated measure using IF accross two tables that have a relationship.

ProjPoints = IF('SportsIOSlates'[DfsSlatePlayers.OperatorRosterSlots] = "CPT", SUM(SportsIOSlates[Salary]) * 1.5, SUM(SportsIOSlates[Salary])
I get the following error message. Not sure what aggregate I would use to determine the value in the row.
 
projpts.png
1 ACCEPTED SOLUTION

Hi , @dfsadvantage 

Please check if these formula help.

 

FantasyPointsDraftKings = CALCULATE( SUM(SportsIOSlatePlayerProj[FantasyPointsDraftKings]),ALLEXCEPT(SportsIOSlates,SportsIOSlatePlayerProj[UniqueID]))
ProjPoints = 
var FantasyPointsDraftKings1=CALCULATE(SUM(SportsIOSlates[FantasyPointsDraftKings]),ALLEXCEPT(SportsIOSlates,SportsIOSlates[DfsSlatePlayers.OperatorRosterSlots]))
return IF('SportsIOSlates'[DfsSlatePlayers.OperatorRosterSlots] = "CPT", FantasyPointsDraftKings1 * 1.5, FantasyPointsDraftKings1)

59.png

 

 

pbix attached 

If it doesn't meet your requirement,please share your sample data and expected result.

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
AntrikshSharma
Resident Rockstar
Resident Rockstar

ProjPoints =
VAR Something =
    SELECTEDVALUE ( 'SportsIOSlates'[DfsSlatePlayers.OperatorRosterSlots], "CPT" )
VAR TotalSalary =
    SUM ( SportsIOSlates[Salary] )
VAR Result =
    IF ( Something = "CPT", TotalSalary * 1.5, TotalSalary )
RETURN
    Result
v-easonf-msft
Community Support
Community Support

Hi , @dfsadvantage 

It looks like a formula of calculated column rather than a measure. 

73.png

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I think a column is the right approach. My Original post was slightly off.....I made adjustments as follows, and instead of a measure (Measures were way too slow as there are many records) so I am trying to introduce it as a column. The formula spans 2 tables, and now I am getting the same resulting value for every row.

 

I have a relationship between these two tables called UniqueID

ProjPoints = IF(VALUES('SportsIOSlates'[DfsSlatePlayers.OperatorRosterSlots]) = "CPT", SUM(SportsIOSlates[Salary]) * 1.5, SUM(SportsIOSlates[Salary])

Hi , @dfsadvantage 

Please check if these formula help.

 

FantasyPointsDraftKings = CALCULATE( SUM(SportsIOSlatePlayerProj[FantasyPointsDraftKings]),ALLEXCEPT(SportsIOSlates,SportsIOSlatePlayerProj[UniqueID]))
ProjPoints = 
var FantasyPointsDraftKings1=CALCULATE(SUM(SportsIOSlates[FantasyPointsDraftKings]),ALLEXCEPT(SportsIOSlates,SportsIOSlates[DfsSlatePlayers.OperatorRosterSlots]))
return IF('SportsIOSlates'[DfsSlatePlayers.OperatorRosterSlots] = "CPT", FantasyPointsDraftKings1 * 1.5, FantasyPointsDraftKings1)

59.png

 

 

pbix attached 

If it doesn't meet your requirement,please share your sample data and expected result.

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi , @dfsadvantage 

Could you please tell me whether your problem has been solved?
If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.

 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@dfsadvantage , measure can only take measure

 

ProjPoints = IF(max('SportsIOSlates'[DfsSlatePlayers.OperatorRosterSlots]) = "CPT", SUM(SportsIOSlates[Salary]) * 1.5, SUM(SportsIOSlates[Salary]))

or


ProjPoints = calculate( SUM(SportsIOSlates[Salary]) * 1.5,'SportsIOSlates'[DfsSlatePlayers.OperatorRosterSlots] = "CPT") +calculate( SUM(SportsIOSlates[Salary]) * 1.5,'SportsIOSlates'[DfsSlatePlayers.OperatorRosterSlots] <> "CPT")

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
Ashish_Mathur
Super User
Super User

Hi,

Does any of these measures work?

ProjPoints = IF(VALUES('SportsIOSlates'[DfsSlatePlayers.OperatorRosterSlots]) = "CPT", SUM(SportsIOSlates[Salary]) * 1.5, SUM(SportsIOSlates[Salary])

ProjPoints = IF(HASONEVALUE('SportsIOSlates'[DfsSlatePlayers.OperatorRosterSlots]),IF(VALUES('SportsIOSlates'[DfsSlatePlayers.OperatorRosterSlots]) = "CPT", SUM(SportsIOSlates[Salary]) * 1.5, SUM(SportsIOSlates[Salary])),BLANK())


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

My Original post was slightly off.....I made adjustments as follows, and instead of a measure (Measures were way too slow as there are many records) so I am trying to introduce it as a column. The formula spans 2 tables, and now I am getting the same resulting value for every row.

 

As measures - perfrmance was a issue:

ProjPoints = IF(VALUES('SportsIOSlates'[DfsSlatePlayers.OperatorRosterSlots]) = "CPT", SUM(SportsIOSlates[Salary]) * 1.5, SUM(SportsIOSlates[Salary])

ProjPoints = IF(HASONEVALUE('SportsIOSlates'[DfsSlatePlayers.OperatorRosterSlots]),IF(VALUES('SportsIOSlates'[DfsSlatePlayers.OperatorRosterSlots]) = "CPT", SUM(SportsIOSlatePlayerProj[FantasyPointsDraftKings]) * 1.5, SUM(SportsIOSlatePlayerProj[FantasyPointsDraftKings]))
,BLANK())

lbendlin
Super User
Super User

Many ways to do that . here is an example

 

ProjPoints = 
var s=selectedvalue(SportsIOSlates[DfsSlatePlayers.OperatorRosterSlots])
return IF(s = "CPT", SUM(SportsIOSlates[Salary]) * 1.5, SUM(SportsIOSlates[Salary])

 

But - this measure probably won't work as it has a single line filter context.

My Original post was slightly off.....I made adjustments as follows, and instead of a measure (Measures were way too slow as there are many records) so I am trying to introduce it as a column. The formula spans 2 tables, and now I am getting the same resulting value for every row.

 

ProjPoints =
var s=selectedvalue(SportsIOSlates[DfsSlatePlayers.OperatorRosterSlots])
return IF(s = "CPT", SUM(SportsIOSlatePlayerProj[FantasyPointsDraftKings]) * 1.5, SUM(SportsIOSlatePlayerProj[FantasyPointsDraftKings])

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!