Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |