Forum Discussion

Diego-mx's avatar
Diego-mx
Advocate I
7 years ago
Solved

Call column from calculated VAR table


I am using a virtual relation to filter entries in my Cars table. 

Purchased_SoFar ...
RETURN CALCULATE( COUNT('Cars'[car_id]) ), 
    TREATAS( VALUES( 'SubCalendar'[a_date]), 'Cars'[purchase_date]) )

However I need to filter the Calendar table beforehand for the SoFar part...   

VAR SubCalendar = CALCULATETABLE('Calendar', 
    FILTER( ALL('Calendar'[date_day]), 
        'Calendar'[date_day] <= MAX('Calendar'[date_day]))))

(together with other slicer values like [month_date]. )

I am getting an error for not finding the table SubCalendar. 


How can I call the columns from the calculated table?
Thanks


Need Help
Tips and Tricks
  • Diego-mx's avatar
    Diego-mx
    7 years ago

    Hello, thank you for the answer. 
    In the formula [date_day] refers to the day of the month but I also have [a_date] which would be the one related to [purchased_date].

    I have tried your suggested answer, but TREATAS won't recognize the corresponding column to match with my related column. 

    I actually figured it out via an extra SELECTCOLUMNS

    VAR Subtable = SELECTCOLUMNS(
    CALCULATETABLE( 'Calendar', 
        FILTER(ALL('Calendar'[date_day]), 'Calendar'[date_day] <= MAX( 'Calendar'[date_day]) )), 
    "a_date", [a_date] )

    Thanks. 

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Diego-mx,

     

    The treatas-function works with two unrelated tables. 

    1. Are there any relationships between the two tables? If so, You don't need a VAR parameter.

     

    Measure =
CALCULATE (
    COUNT ( 'Cars'[car_id] ),
    FILTER (
        ALL ( 'Calendar'[date_day] ),
        'Calendar'[date_day] <= MAX ( 'Calendar'[date_day] )
    )
)

     

    2. If you can't establish a relationship, the formula could be like below.

     

    Measure =
CALCULATE (
    COUNT ( 'Cars'[car_id] ),
    TREATAS (
        FILTER ( 'Calendar', 'Calendar'[date_day] <= MAX ( 'Calendar'[date_day] ) ),
        'Cars'[purchase_date]
    )
)

     

    Best Regards,
    Dale

    • Diego-mx's avatar
      Diego-mx
      Advocate I
      7 years ago

      Hello, thank you for the answer. 
      In the formula [date_day] refers to the day of the month but I also have [a_date] which would be the one related to [purchased_date].

      I have tried your suggested answer, but TREATAS won't recognize the corresponding column to match with my related column. 

      I actually figured it out via an extra SELECTCOLUMNS

      VAR Subtable = SELECTCOLUMNS(
    CALCULATETABLE( 'Calendar', 
        FILTER(ALL('Calendar'[date_day]), 'Calendar'[date_day] <= MAX( 'Calendar'[date_day]) )), 
    "a_date", [a_date] )

      Thanks.