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

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.