cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Diego-mx Regular Visitor
Regular Visitor

Returning a table with TREATAS

I have a main Calendar table with at least three columns. 

a_date,     month_date, week_date
2018-01-01, 2018-01-01, 2018-01-01
2018-01-02, 2018-01-01, 2018-01-01
...
2018-01-08, 2018-01-01, 2018-01-08
2018-01-09, 2018-01-01, 2018-01-08
...
2018-02-01, 2018-02-01, 2018-01-29
...

 

Then, with useful help from the community on this other post, I created a secondary Calendar2 table with dates corresponding to the second and third columns of the first one. 

b_date,    period
2018-01-01, month
2018-02-01, month
2018-03-01, month 
... 
2018-01-01, week
2018-01-08, week
2018-01-15, week
2018-01-22, week
... 

I want to use that Calendar2 table in order to filter the main Calendar table.  
And thus I found this blogpost which explains the use of TREATAS function, which seems to be the tool I need.  
I came up with the following: 

 

Calendar3 = IF( HASONEVALUE( Calendar2[period] ), 
    SWITCH ( VALUES( Calendar2[period]), 
    "month", TREATAS( VALUES( Calendar2[b_date] ), 'Calendar'[month_date]), 
    "week",  TREATAS( VALUES( Calendar2[b_date] ), 'Calendar'[week_date] )) )

 

That is supposed to be a filtered copy of Calendar1, according to the values in Calendar2 but depending on the corresponding periodicity.  Does this make sense?  

While the examples I've seen use the result of TREATAS inside a CALCULATE function, I would like the result to be a standing table on its own.  

Thank you for your help. 


2 REPLIES 2
Community Support Team
Community Support Team

Re: Returning a table with TREATAS

Hi @Diego-mx,

 


Calendar3 = IF( HASONEVALUE( Calendar2[period] ), 
    SWITCH ( VALUES( Calendar2[period]), 
    "month", TREATAS( VALUES( Calendar2[b_date] ), 'Calendar'[month_date]), 
    "week",  TREATAS( VALUES( Calendar2[b_date] ), 'Calendar'[week_date] )) )

 

That is supposed to be a filtered copy of Calendar1, according to the values in Calendar2 but depending on the corresponding periodicity.  Does this make sense?  


I think this is not possible. As you can see, in the example, the result of TREATAS is inside a CALCULATE function, the final result is returned by a measure which can be dynamically changed based on slicer. While, in your attemption, you created a calculated table with TREATAS which is static once it is initilized. 

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Diego-mx Regular Visitor
Regular Visitor

Re: Returning a table with TREATAS

Thanks Yuliana, 
I see that you also helped me in my previous post.  So I modified this one above, to add background and credit.  
In any case, it seems that TREATAS is getting me to close what I want. 
I'm gonna need to try a bit more, before closing it.   

Regards.