Forum Discussion

CarlBlunck's avatar
CarlBlunck
Icon for Resolver I rankResolver I
3 years ago
Solved

A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter exp

Hello,

 

I am getting this error and unsure how to resolve.  Have tried a solution in another post but still no luck.

 

Original dax:

 

Overtime hours between = 
VAR StartHour = SELECTEDVALUE(DistinctStartingHour[Start of Hour])
VAR EndHour = SELECTEDVALUE(DistinctEndHour[End Of Hour])
RETURN
IF([StartHour] > [EndHour] ,
--calculate overtime that crosses midnight
CALCULATE([Overtime hours],
fact_TimeCardData[STARTTIME] >= StartHour && fact_TimeCardData[EndTimeAdjusted] <= TIME(23,59,0)
)
+
CALCULATE([Overtime hours],
fact_TimeCardData[STARTTIME] >= TIME(0,0,0) && fact_TimeCardData[EndTimeAdjusted] <= [EndHour]
)
+
CALCULATE([Overtime hours],
fact_TimeCardData[STARTTIME] >= StartHour && fact_TimeCardData[EndTimeAdjusted] <= EndHour
)
,
--calculate overtime that does not cross midnight
CALCULATE([Overtime hours],
fact_TimeCardData[STARTTIME] >= StartHour && fact_TimeCardData[EndTimeAdjusted] <= EndHour
))

 

 

Updated dax from solution, still not working:

 

Overtime hours between = 
VAR StartHour = SELECTEDVALUE(DistinctStartingHour[Start of Hour])
VAR EndHour = SELECTEDVALUE(DistinctEndHour[End Of Hour])
RETURN
IF([StartHour] > [EndHour] ,
--calculate overtime that crosses midnight
CALCULATE([Overtime hours],
FILTER(fact_TimeCardData,fact_TimeCardData[STARTTIME] >= StartHour && fact_TimeCardData[EndTimeAdjusted] <= TIME(23,59,0))
)
+
CALCULATE([Overtime hours],
FILTER(fact_TimeCardData,fact_TimeCardData[STARTTIME] >= TIME(0,0,0) && fact_TimeCardData[EndTimeAdjusted] <= [EndHour])
)
+
CALCULATE([Overtime hours],
FILTER(fact_TimeCardData,(fact_TimeCardData[STARTTIME] >= StartHour && fact_TimeCardData[EndTimeAdjusted] <= EndHour)
)
,
--calculate overtime that does not cross midnight
CALCULATE([Overtime hours],
FILTER(fact_TimeCardData,fact_TimeCardData[STARTTIME] >= StartHour && fact_TimeCardData[EndTimeAdjusted] <= EndHour)
)))

 

 

Appreciate the assist.

 

Thanks

Carl

Need Help
  • CarlBlunck's avatar
    CarlBlunck
    3 years ago

    This worked:

     

    Overtime hours between = 
VAR StartHour = SELECTEDVALUE(DistinctStartingHour[Start of Hour])
VAR EndHour = SELECTEDVALUE(DistinctEndHour[End Of Hour])
RETURN
IF([StartHour] > [EndHour] ,
--calculate overtime that crosses midnight
CALCULATE([Overtime hours],
FILTER(fact_TimeCardData,fact_TimeCardData[STARTTIME] >= StartHour && fact_TimeCardData[EndTimeAdjusted] <= TIME(23,59,0))
)
+
CALCULATE([Overtime hours],
FILTER(fact_TimeCardData,fact_TimeCardData[STARTTIME] >= TIME(0,0,0) && fact_TimeCardData[EndTimeAdjusted] <= [EndHour])
)
+
CALCULATE([Overtime hours],
FILTER(fact_TimeCardData,(fact_TimeCardData[STARTTIME] >= StartHour && fact_TimeCardData[EndTimeAdjusted] <= EndHour))
)
,
--calculate overtime that does not cross midnight
CALCULATE([Overtime hours],
FILTER(fact_TimeCardData,fact_TimeCardData[STARTTIME] >= StartHour && fact_TimeCardData[EndTimeAdjusted] <= EndHour)
)
)

3 Replies

  • ppm1's avatar
    ppm1
    Icon for Solution Sage rankSolution Sage
    3 years ago

    There is likely a simpler way to write your measure, but this version will be more performant (it is bad practice to filter entire tables) and may address your error.

     

    Overtime hours between =
VAR StartHour =
    SELECTEDVALUE ( DistinctStartingHour[Start of Hour] )
VAR EndHour =
    SELECTEDVALUE ( DistinctEndHour[End Of Hour] )
RETURN
    IF (
        StartHour > EndHour,
        --calculate overtime that crosses midnight
        CALCULATE (
            [Overtime hours],
            fact_TimeCardData[STARTTIME] >= StartHour,
            fact_TimeCardData[EndTimeAdjusted] <= TIME ( 23, 59, 0 )
        )
            + CALCULATE (
                [Overtime hours],
                fact_TimeCardData[STARTTIME] >= TIME ( 0, 0, 0 ),
                fact_TimeCardData[EndTimeAdjusted] <= EndHour
            )
            + CALCULATE (
                [Overtime hours],
                fact_TimeCardData[STARTTIME] >= StartHour,
                fact_TimeCardData[EndTimeAdjusted] <= EndHour
            ),
        --calculate overtime that does not cross midnight
        CALCULATE (
            [Overtime hours],
            fact_TimeCardData[STARTTIME] >= StartHour,
            fact_TimeCardData[EndTimeAdjusted] <= EndHour
        )
    )

     

    Pat

    • CarlBlunck's avatar
      CarlBlunck
      Icon for Resolver I rankResolver I
      3 years ago

      Thanks Pat, unfortunately this returned really weird results....

  • CarlBlunck's avatar
    CarlBlunck
    Icon for Resolver I rankResolver I
    3 years ago

    This worked:

     

    Overtime hours between = 
VAR StartHour = SELECTEDVALUE(DistinctStartingHour[Start of Hour])
VAR EndHour = SELECTEDVALUE(DistinctEndHour[End Of Hour])
RETURN
IF([StartHour] > [EndHour] ,
--calculate overtime that crosses midnight
CALCULATE([Overtime hours],
FILTER(fact_TimeCardData,fact_TimeCardData[STARTTIME] >= StartHour && fact_TimeCardData[EndTimeAdjusted] <= TIME(23,59,0))
)
+
CALCULATE([Overtime hours],
FILTER(fact_TimeCardData,fact_TimeCardData[STARTTIME] >= TIME(0,0,0) && fact_TimeCardData[EndTimeAdjusted] <= [EndHour])
)
+
CALCULATE([Overtime hours],
FILTER(fact_TimeCardData,(fact_TimeCardData[STARTTIME] >= StartHour && fact_TimeCardData[EndTimeAdjusted] <= EndHour))
)
,
--calculate overtime that does not cross midnight
CALCULATE([Overtime hours],
FILTER(fact_TimeCardData,fact_TimeCardData[STARTTIME] >= StartHour && fact_TimeCardData[EndTimeAdjusted] <= EndHour)
)
)
Related content