If Conditions in a measure

[dfsadvantage]

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.

[AntrikshSharma]

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]

Hi , @dfsadvantage 

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

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.

[dfsadvantage]

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

[v-easonf-msft · 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)

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.

[v-easonf-msft]

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.

[Ashish_Mathur]

Hi,

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

[amitchandak]

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

[Ashish_Mathur]

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

[dfsadvantage]

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]

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.

[dfsadvantage]

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