Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

The True/False expression does not specify a column. Each True/False expression used as a table

Hi..I am trying to figure out why this doesn't work.  I'm trying to create a measure that will show actual + forecast ONLY for the current year of the selected Month-End Date filter.  I have a TTM measure that works just fine, which I tried to modify for my current year.  I am getting a T/F error for this part.  I'm obviously not understanding how my _Year Variable needs to be changed, as that's the only thing that is different.  Any help is appreciated!

VAR _Year=(SELECTEDVALUE('Date'[Year]))

Var LastActualsdate=calculate(Max('IT Expense'[EOM]),'IT Expense'[Scenario]="Actual",REMOVEFILTERS())

VAR Actuals=calculate([C&M Costs-Actuals],REMOVEFILTERS('Date'),KEEPFILTERS(_Year),USERELATIONSHIP('Date'[Date],'Disconnect Date'[Date]))

Return Actuals
BUT this works just fine.  

TTM Actual C&M = VAR _SelDate=Max('Date'[Date])

VAR _PV13=DATESINPERIOD('Disconnect Date'[Date],_SelDate,-13,Month)

VAR _Result=Calculate([C&M Costs-Actuals],

REMOVEFILTERS('Date'),

KEEPFILTERS(_PV13),

USERELATIONSHIP('Date'[Date],'Disconnect Date'[Date]))

Return

_Result

 

Need Help
  • Wilson_'s avatar
    Wilson_
    2 years ago

    Hi atowriss99,

     

    SELECTEDVALUE returns a singular value, which is not compatible as a parameter of KEEPFILTERS. You're probably looking for something like KEEPFILTERS ( 'Date'[Year] = _Year ) or something.


    ----------------------------------
    If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

  • Anonymous's avatar
    Anonymous
    2 years ago

    I finally figured it out!  I used this:

    Full Year C&M Actuals =
    Calculate([C&M Costs-Actuals],Filter(All('Date'),'Date'[Year]=Year(SELECTEDVALUE('Date'[End of Month]))),USERELATIONSHIP('Date'[Date],'Disconnect Date'[Date]))

4 Replies

  • Wilson_'s avatar
    Wilson_
    Memorable Member
    2 years ago

    Hi atowriss99,

     

    SELECTEDVALUE returns a singular value, which is not compatible as a parameter of KEEPFILTERS. You're probably looking for something like KEEPFILTERS ( 'Date'[Year] = _Year ) or something.


    ----------------------------------
    If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I finally figured it out!  I used this:

      Full Year C&M Actuals =
      Calculate([C&M Costs-Actuals],Filter(All('Date'),'Date'[Year]=Year(SELECTEDVALUE('Date'[End of Month]))),USERELATIONSHIP('Date'[Date],'Disconnect Date'[Date]))
      • Wilson_'s avatar
        Wilson_
        Memorable Member
        2 years ago

        atowriss99,

         

        Awesome, glad you were able to figure it out. 😄

         

        A few suggestions from me:

         

        1. Format your measures instead of leaving them all as one continuous line so the code is more readable. Your future self (and other people) will thank you. 😄
        2. For performance reasons, I believe it's better to keep your _Year variable and use it in your FILTER statement. Variables will calculate only one time if you do.
        3. I'm guessing you have a Year column in your Date table. You should be able to just use SELECTEDVALUE ( 'Date'[Year] ) instead of capturing the date, then calculating the year from the date.

        Maybe something like the below:

        Full Year C&M Actuals =
VAR _Year = SELECTEDVALUE ( 'Date'[Year] )
CALCULATE (
    [C&M Costs-Actuals],
    FILTER (
        ALL ( 'Date' ),
        'Date'[Year] = _Year
    ),
    USERELATIONSHIP ( 'Date'[Date], 'Disconnect Date'[Date] )
)

         

        Just my thoughts. 😄


        ----------------------------------
        If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)