cancel
Showing results for
Did you mean:
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.

1 ACCEPTED SOLUTION
Community Support

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

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.

11 REPLIES 11
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``````
Community Support

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.

Frequent Visitor

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

Community Support

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

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.

Community Support

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.

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
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")

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

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

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.

Frequent Visitor

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

Announcements

#### 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 Design Challenge

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

#### 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.