cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mark973 Regular Visitor
Regular Visitor

Create second Calendar table from an original Calendar table

Hi -

 

I a have calendar table with with two columns, Date [=Calendar(Date(2015, 1, 1), Date(2018, 12, 31)] and a Measure [= "Y" or "N"].

 

I want create a second calendar table that includes the Dates from the original where the measure equals "Y" (see tables below).

 

Thank you, Mark 

 

Original Table Second Table
DateMeasure Date
1/1/2015N  
1/2/2015N  
1/3/2015Y 1/3/2015
1/4/2015Y===>1/4/2015
:  :
12/30/2018Y 12/30/2018
12/31/2018N  
13 REPLIES 13
Super User
Super User

Re: Create second Calendar table from an original Calendar table

So, you should be able to do that like this:

 

New Calendar = 
VAR __table = ADDCOLUMNS(ALL('Date'),"__Measure",[Measure])
RETURN
FILTER(__table,[__Measure] = "Y")

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Mark973 Regular Visitor
Regular Visitor

Re: Create second Calendar table from an original Calendar table

I will give that a try .  Thank you.  Just such a newbe to DAX, "simple" things take questions to learn.

Mark973 Regular Visitor
Regular Visitor

Re: Create second Calendar table from an original Calendar table

Hi Greg - The new calendar didn't seem to work, as it seemd to ignore the filter, and shows the entire range of the original calendar.

 

Would you have any suggestions?

 

The sample code was adjusted as shown below ... 

 

NEW_CALENDAR = 
VAR __table = ADDCOLUMNS(ALL('CALENDAR'[Date]),"__Measure",'CALENDAR'[Is In Date Range])
RETURN
FILTER('CALENDAR','CALENDAR'[Is In Date Range] = "Y")

A picture of the original calendar and new calander is below.  I am wanting the New Calendar to show only 5/12/2018 to 5/20/2018

 

Capture10.PNG

Super User
Super User

Re: Create second Calendar table from an original Calendar table

@Mark973, not sure, I created it in my test environment and it seems to work. See attached Page 10. Calendar, New Calendar and Measure 4.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Mark973 Regular Visitor
Regular Visitor

Re: Create second Calendar table from an original Calendar table

Hi Greg - I didn't see any attachments (Go Bucks! ... I see you are from Columbus.)

Super User
Super User

Re: Create second Calendar table from an original Calendar table

OK, let me try that again.

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Mark973 Regular Visitor
Regular Visitor

Re: Create second Calendar table from an original Calendar table

Got it.  Thanks.   And yes your example works.

 

In my example, the indicator (Measure4) in Calendar is dynamic based on a slicer value so the dates with "Y" (Measure4) changes from slicer to value to value.  And for whatever the reason, Measure4 in New Calendar won't change and mirror what is in Calendar.  

 

Thanks for your help.

 

 

 

 

Super User
Super User

Re: Create second Calendar table from an original Calendar table

@Mark973 - Yes, unfortunately that is correct. A calculated table is not dynamic, it only updates upon data refresh. Let me think about that and what you are trying to accomplish. Can you provide a use case? Because it should be possible to generate a measure that essentially filters your table correctly and provides the appearance of another calendar table but the usefulness of that is going to depend on what you are actually trying to accomplish.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Mark973 Regular Visitor
Regular Visitor

Re: Create second Calendar table from an original Calendar table

@Greg_Deckler

 

I can provide a pbix file which will have have the exact specifics of what I am trying to accomplish.  Is that what you mean by 
"use case"? 

 

I will try to get that this evening.

 

Thanks, Mark