Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 | ||
Date | Measure | Date | |
1/1/2015 | N | ||
1/2/2015 | N | ||
1/3/2015 | Y | 1/3/2015 | |
1/4/2015 | Y | ===> | 1/4/2015 |
: | : | ||
12/30/2018 | Y | 12/30/2018 | |
12/31/2018 | N |
So, you should be able to do that like this:
New Calendar = VAR __table = ADDCOLUMNS(ALL('Date'),"__Measure",[Measure]) RETURN FILTER(__table,[__Measure] = "Y")
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
@Anonymous, not sure, I created it in my test environment and it seems to work. See attached Page 10. Calendar, New Calendar and Measure 4.
Hi Greg - I didn't see any attachments (Go Bucks! ... I see you are from Columbus.)
OK, let me try that again.
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.
@Anonymous - 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.
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
By use case, I just trying to understand why you need this second table. Is it just for display purposes or are you using it in another calculation or ?
Hi Greg - I really appreciate your comments on this subject. Thank you. Mark
Ok. I thought I posted this last night, but apparently the connection was lost and the upload failed (which I discovered this morning).
Short Answer:
The "New_Calendar" supports visuals.
More Words:
User makes selection from Slicer which is connected to a table with start and end dates. The selected start and end dates populate a measure (= Measure4 in your example) in table "Calendar" which really shows the range of dates. The "Calendar" table is the Base date table (if you will) and spans a large time frame of say 1/1/21016 to 12/31/2026. That range of dates (usually a rolling 2 year period) is used to populate "New_Calendar" (in DAX jargon this is referred to as a Date Dimension table). The report's visuals run off of "New_Calendar".
Other Details:
The table connected to the Slicer is downloaded from a SQL database (refreshed daily). The New_Calendar will need other date related columns (e.g., Fiscal Year, etc. which I still need to figure out) around the Date range defined by Measure4.
I wanted to upload a pbix file that has shows it (but alas don't have the permission to upload the files).
@Anonymous - OK, so crazy thought, why not just use a measure that returns N if it is not in the date range and Y if it is and use that as a visual filter instead of creating an entirely new table?
Thank you for the idea, I will take a look at it. Simple always beats Complicated.
I will give that a try . Thank you. Just such a newbe to DAX, "simple" things take questions to learn.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |