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

DAX: allselected did not pass the filter

i have this dax function to calculate page views same period from last year

 

page_view = var a = DATE(MAX('page views final'[CreatedOn].[Year])-1,MAX('page views final'[CreatedOn].[MonthNo]),MAX('page views final'[CreatedOn].[Day]))
var b = DATE(MIN('page views final'[CreatedOn].[Year])-1,MIN('page views final'[CreatedOn].[MonthNo]),MIN('page views final'[CreatedOn].[Day]))
Return
CALCULATE(COUNTROWS('page views final'),FILTER(allselected('page views final'),[CreatedOn]<=a&&[CreatedOn]>=b))
 
when i want to select 2019 in my slicer (based on "CreatedOn"), there is only blank which is not correct. 
 
can someone help me with this issue?
 
thank you!
6 REPLIES 6
Geradav
Responsive Resident
Responsive Resident

Hi @Anonymous ,

 

I agree with @Anonymous, it is good practice to organize your data model with a dedicated date table and create a relationship with your other table.

Then you use a CALCULATE() function with a SAMEPERIODLASTYEAR() function

Annotation 2019-08-02 220841.jpg

 

It would look something like that

Measure from last year =
CALCULATE ( [MyMeasure], SAMEPERIODLASTYEAR ( calendarTbl[Date] ) )

For more info on SAMEPERIODLASTYEAR() check this page https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

 

Let us know if you need more help in refining your model

Anonymous
Not applicable

this does not work in my situation. actually i've already tried this function. that's why i have to use a long dax code. i basically defind these variables and it works 80%. it's just when i select the date slicer, the goal did not change as expected. it became blank. and when i change another slicer, it works. i would love to go with my current solution because it's so close. 

 

thank you so much!

Geradav
Responsive Resident
Responsive Resident

Hi @Anonymous 

 

Can you provide some sample data?

Anonymous
Not applicable

this is how it looks like: when i did not select anything in 'View Date', it shows 82679 in indicator which is all data from 2018 and 19 and (31300 which is 2018 data). 

Capture.PNG

but when i select 2019, the perfect situation is indicator 51379 and goal should be 31300, but it shows blank. i really think it's very close to what i want. may just be some slight change. i created two measures for indicator and goal, and they are in my first threshold.

 

Capture.PNG

Geradav
Responsive Resident
Responsive Resident

Hi @Anonymous 

 

See the illustration below if that can be of help

Untitled Project.gif

If that still doesn't work for you, you could send me the file with some sample data at david@fumastra.com

Anonymous
Not applicable

I'd start by getting a dedicated date table and relate that your fact table. Then you can leverage the time intelligence functions, or at least make writing the dax easier and increase performance. 

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.