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
elouizi
Helper II
Helper II

Filter function only shows 1 day rather than 7 days in table

Hi Guys,

 

download pbix file: https://mega.nz/#!1bIB3IwS!pixVVyouIY77yU_pprPaTLvM0l1ZcqLODRILLgCba6U 

 

I have created a messaure that calculates the average IO over a period of 7 days. I used the filter function and date add to substract 7 days of the orignal date. My table is only showing the selected date - 7 days and not all the days between them.

 

Basically when a user selects a date, the table should show the average IO of the past 7 days,

 

Hope some one can help me out.

 

 

 

1 ACCEPTED SOLUTION
dax
Community Support
Community Support

Hi @elouizi , 

I am not sure whether this is what you want, you could refer to my sample for details(create a calendar table and use this in slicer, no relationship between tables). If this is not what you want, please correct me and inform me more detailed information(such as your expected output and your sample data (OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

8 REPLIES 8
dax
Community Support
Community Support

Hi @elouizi , 

I am not sure whether this is what you want, you could refer to my sample for details(create a calendar table and use this in slicer, no relationship between tables). If this is not what you want, please correct me and inform me more detailed information(such as your expected output and your sample data (OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

Hi,

 

please find my power bi file. 

https://mega.nz/file/YHwhGKDI#eEhd3sLvy9yZEYhszg8qRpAHag9Ubguz23HC0oPGgF4 

I used the messaure in my actual data. But strangly I see nothing.
What am I doing wrong. I created a calender table. and copied your messaure.
No errors, but also no data even though I know there is data.

There is no relationship between the two.

 

Looking forward for your help

 

Hi,

 

I do not see your problem, as CST gave you the formula already ?

I tested with and it seems to work, isn't it ? :

avg_mem_7_dagen2 = CALCULATE(AVERAGE(WhoisActive_AGGR_v[AVG_used_memory]); CROSSJOIN(WhoisActive_AGGR_v;Kalender);
DATESINPERIOD(Kalender[DateKEY];SELECTEDVALUE(WhoisActive_AGGR_v[date]);-7;DAY))

Just replace my ; by your ,.

 

Shoudl do the job, let us know 

Hi,

 

The problem that I have is that I only see one date and not the last 7 days.
I just copied the formula, but the results are not the same.

any ideas what I am doing wrong?

 

Many thanks!

dax
Community Support
Community Support

Hi  @elouizi , 

You need to use calendar in slicer and you need to make sure you use the date type, don't use format function. You could refer to my sample for details.

Best Regards,
Zoe Zhi

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

Thank you. I just dont understand why it is nessary to create a date table before you can do such calculation. 
I know that with other BI tools, where it not a nessasity.

 

AnyWayz, 

Thank you.

 

For your help.

AilleryO
Memorable Member
Memorable Member

Hi,

 

It seems you don't have a Calendar Table in your model, which will be a problem to manipulate Time Intelligence functions.

 

To create one you have many tutorials and it takes only a few seconds :

One amongst many : https://powerbi.tips/2017/11/creating-a-dax-calendar/

 

One your table is created and linked, you will be able to make formulas like this one :

avg_mem_7_dagen_V2 = CALCULATE(AVERAGE(AVG_IO[AVG_IO]); CROSSJOIN(AVG_IO;'MyCalendar');
DATESINPERIOD('MyCalendar'[DateRef];SELECTEDVALUE(AVG_IO[date]);-7;DAY))

The CROSSJOIN, helps you te select only the date in correspondance.

And the DATESINPERIOD select the dates you need.

Hope it helps,

AilleryO
Memorable Member
Memorable Member

Hi,

 

Instead of DATEADD, did you try with DATESINPERIOD ?

 

Have  a nice day

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.