Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Create dynamic table based on dropdown filter choice

Hi, 
I'm trying to create a dynamic table as a data source in my report using CALENDAR() function. I'm using this function: 
 
CALENDAR(DATE(YEAR([start_date]),month([start_date]),day([start_date])),DATE(YEAR([end_date]),month([end_date]),day([end_date])))
 
where, 
[start_date] & [end_date] are measures calculated based on a dropdown filter.
For eg: if dropdown filter selection is "Last Week", then 
[start_date]: 13th July,2020
[end_date]: 19th July,2020
 
Thus, the calendar table should contain all date values between 13th July,2020 and 19th July,2020 in a column. But somehow this calendar table is giving incorrect date range (starting with April everytime)  Could you please help with this?
 
Thanks in advance!!
1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

Has your problem been solved?

Please refer to my .pbix file.

v-lionel-msft_0-1596522846229.png

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-lionel-msft
Community Support
Community Support

Hi @Anonymous ,

 

You should create relationship between the two tables. Please refer to my .pbix file.

v-lionel-msft_0-1595296235443.png

v-lionel-msft_1-1595296247597.png

v-lionel-msft_2-1595296265144.png

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

Hi @v-lionel-msft 

 

Thanks for this solution, but my date table (which is used to calculate date measures) is in this format: 

 

Period                         Start Date            End Date

Last Week                 13th July,2020      19th July,2020

Last Month                1st Jun,2020         30th Jun,2020

 

There is no date column which can be used to create direct relationship between this and the dynamic table.

mahoneypat
Employee
Employee

While you can make a dynamic table inside a measure, the Calendar table in your model is not dynamic.  It only refreshes when you refresh the data model (not with slicer selections).  Also, why not use CALENDAR(MIN(Table[DateColumn]), MAX(Table[SameorDifferentDateColumn])) ?

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


TomMartens
Super User
Super User

Hey @Anonymous ,

 

I guess it's not possible what you are going to achieve. This is because virtual tables (tables based on DAX) will only be calculated if the data model is refreshed, or when a DAX statement is changed.

 

For this reason, a virtual table will not be recalculated when a user changes the selection of a slicer.

 

Can you please describe in more detail what you are going to achieve, maybe then we can come up with a different approach.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Hi @TomMartens ,

 

Here are my tables: 

 

Table1: 

Period                    Start Date          End_Date

Last Week          13th July,2020          19th July,2020

Last Month          1st Jun ,2020          30th Jun ,2020

 

Table2: KPI table

 

Date                KPI volume

1st Jan,2020        xxx

2nd Jan,2020       xxx

.

.

20th July,2020    xxx

 

I'm looking to create a line trend graph (date vs kpi) for the date period i choose from the dropdown filter. If the filter choice is "Last Week", then the graph shoud be for 13-19 july. 

 

graph.PNG

 

Is there any other way to do this other than creating a dynamic table?

Hi @Anonymous ,

 

Has your problem been solved?

Please refer to my .pbix file.

v-lionel-msft_0-1596522846229.png

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.