Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Need assistance with creating a calculated measure using IF accross two tables that have a relationship.
Solved! Go to 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)
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.
ProjPoints =
VAR Something =
SELECTEDVALUE ( 'SportsIOSlates'[DfsSlatePlayers.OperatorRosterSlots], "CPT" )
VAR TotalSalary =
SUM ( SportsIOSlates[Salary] )
VAR Result =
IF ( Something = "CPT", TotalSalary * 1.5, TotalSalary )
RETURN
Result
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.
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)
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.
@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")
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())
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())
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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
97 | |
80 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |