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
socksinbox
Helper I
Helper I

Filter visual by measure not working

Hi,


I have one measure called "CustomerScore" which i want to filter by another measure (lastquarterscore). Everything seems to working fine but filterining not happening. I couldn't able to drage a measure on Page filter. However, I can drag the measure to Visual Filter but can't able to select anything.

 

I can't share the original report but i am sharing a test report where you can see the problem.

 

Please help. I can calculate the filtered measure directly in DAX but I don't want that. i just want to filter the page by a calculated measure.

 

1 ACCEPTED SOLUTION

Right I now understand what your issue is. You can fix that by creating a TRUE/FALSE column in your Calendar1 table like this:

IsLastQuarter = 
VAR _today = FILTER(ALL(Calendar1), Calendar1[Date] = TODAY())
VAR prevQ = CALCULATETABLE(PREVIOUSQUARTER(Calendar1[Date]), _today)
RETURN
IF(Calendar1[Date] IN prevQ, TRUE, FALSE)

Then filter your page on the column IsLastQuarter and then your datetable is always filtered to the last quarter.

On your other question; PREVIOUSQUARTER() takes a table as input and TODAY() returns a datetime value and thus is not valid. A filtered table derived from Calendar1 is a valid input. Hope this helps you out!

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





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

Proud to be a Super User!




View solution in original post

12 REPLIES 12
socksinbox
Helper I
Helper I

The reason why you can't filter on that measure is because you would create an circular reference. The Measure is evaluated based on the filtered context, but then you want tog filter te context based on the measure (which would be re-evaluated etc). 

However, in your specific case: you say you have two measures (lastquarterscore and CustomerScore). In the pbix I only see one measure (lastquarterscore). I don't understand what you are trying to achieve by 'filtering on lastquarterscore' as that measure returns a scalar value for the current context. What you might need is a calculated column and evaluate for every row in the table a certain expression and then use that column as a filter. 

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

 





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

Proud to be a Super User!




@JarroVGIT  - CustomerScore is a measure i.e. sum of customerscore but not explicitly defined. 

 

Right now, customerscore shows the sum of all the rows. However, I just wanted to show the sum for only last quarter. Currently, last quarter measure is doing it and it's hardcoded for now.

 

So I just want to filter customer score where lastquarter value evalutes to 1.

 

 

 

 

ALright I see what you are trying to achieve, which is this "However, I just wanted to show the sum for only last quarter." 

This is achievable by using the following measure:

LastQ = 
CALCULATE(SUM('Customer Score'[CustomerScore]),
    PREVIOUSQUARTER(Calendar1[Date])
)

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





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

Proud to be a Super User!




Sorry one last point. PREVIOUSQUARTER() takes the first date of the datecolumn you give as input and then give the previous quarter. In your case, you want the previous quarter relative to today. Here is a measure that filters your Date table to today's date and uses that as input for the PREVIOUSQUARTER() input:

LastQ = 
VAR _today = FILTER(VALUES(Calendar1[Date]), Calendar1[Date] = TODAY())
RETURN
CALCULATE(SUM('Customer Score'[CustomerScore]), PREVIOUSQUARTER(_today))

Note that this doesn't work for your testfile as TODAY() will result in a date not available in your Calendar1[Date]. For testing, you can replace TODAY() with TODAY-4 (which evaluates to jan 2nd, as of this writing). 

Let me know if this is what you were looking for 🙂

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





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

Proud to be a Super User!




 

Hi Thanks for this and I knew this could be achieveable directly in measure but imagine if you have multiple numeric field in your table. Is it not good that instead of creating a lastquarter measure for all, just create a 'measure filters and filter and keep that on the page filter. This way if business comes and ask to show last month or something else then we only need to change the measure filter. 

I recall I used to do this in Tableau and it's very easy to filter dimension with calculated measure value instead of a an static values

 

Anyways, thanks for the below. 

LastQ = 
VAR _today = FILTER(VALUES(Calendar1[Date]), Calendar1[Date] = TODAY())
RETURN
CALCULATE(SUM('Customer Score'[CustomerScore]), PREVIOUSQUARTER(_today))

Just wanted to ask onething. Why you need to filter calendar table with Today() ? Can't we directly pass Today() in PreviousQuarter function? 

Right I now understand what your issue is. You can fix that by creating a TRUE/FALSE column in your Calendar1 table like this:

IsLastQuarter = 
VAR _today = FILTER(ALL(Calendar1), Calendar1[Date] = TODAY())
VAR prevQ = CALCULATETABLE(PREVIOUSQUARTER(Calendar1[Date]), _today)
RETURN
IF(Calendar1[Date] IN prevQ, TRUE, FALSE)

Then filter your page on the column IsLastQuarter and then your datetable is always filtered to the last quarter.

On your other question; PREVIOUSQUARTER() takes a table as input and TODAY() returns a datetime value and thus is not valid. A filtered table derived from Calendar1 is a valid input. Hope this helps you out!

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





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

Proud to be a Super User!




Thanks @JarroVGIT. I am trying to create this measure but it's not working- give me below error.

 

https://i.imgur.com/GPiGDPn.png

 

Appreciate if you can do in the test file I shared 

 

 
 

Sorry if this wasn't clear, but the dax is a calculated column in the Calendar1 table, not a measure.
I created it using your file, but had to replay today() with today()-6 because today's date is not in your Calendar1 table but in a reallife scenario it would be.




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

Proud to be a Super User!




Oh. I got that. So there's no way to filter by measure on the visual or page.

 

Thanks alot for helping me.

But why do you need it to be a measure? Using a column is a perfect valid case in your use case and requirements and you can use that to filter on a page level?
Anyway, please mark the solution so others can find answers to the same question more easily:)
Thanks!




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

Proud to be a Super User!




Measure is more efficent in terms of column storage and I just wanted to replicate what I could able to do in Tableau.

 

Anyways, I have already marked the previous post as accepted solution. 

Thanks a lot dear for helping me out

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.

Top Solution Authors