infinity filtering help

[Anonymous]

Hello, 

I've calculated the average for dates not within the last 30 days.

However, I want to apply this average to each line item in the last 30 days on another visual as a static number.

It however, it giving me infinity instead, which is should because of filtering context. Is there anyway to return a dax measure as 1 number and ignore filters applied before saving it?

For example, calculating the average growth rate of cats saving that growth rate,, but putting that same growth rate in a table with rows where animal ="dogs"?

[Anonymous]

I tried the following: Predicted_30d_Buy_from3d = CALCULATE(([3day Conversion]/[3/30 Flow Rate Avg]+0),ALL(Buys),FILTER(ALL('Date'),'Date'[Date]<[Max_Date]-100)) 

to attempt to get rid of the dates that I dont want. However, they still appear within the visual.

[TheoC]

@Anonymous, I'd recommend either breaking up the measure into multiple measures or use variables, just to help with the clarity.  For example, using measures:

1. Measure n1 = [3day Conversion] / [3/30 Flow Rate Avg] + 0

2. Measure n2 = CALCULATE ( {Measure n1] , 'Date'[Date] > Date ( 2021, 9 , 4 ) , ALL ( 'Date' ) )

Let me know how the above goes. Worst case scenario, you can always just click on your visual (i.e. table) and go to the Filters on This Visual. Once you're there, go to Inquiries field, click on Fitler Type = Advanced Filtering and "is not blank" then hit apply. That will remove the blank rows. 

Also, just out of curiousty, the two columns in your table, are they correct?

Let me know how it goes 🙂

[Anonymous]

[TheoC]

@Jordanokstate, one way to achieve it is by adding "FILTER" to the "ALL" function.  For example: 

Measure with Date Filter =
     CALCULATE ( [Measure] ,
          FILTER ( ALL ( Table ) , Table [Date] > DATE ( 2021 , 9 , 4 ) )
                       ) 

Hope this helps mate!

Mate, modified the measure based on seeing your screenshot.  Did not think to ask about the 9/5/2021 lol.  The DATE value should be DATE ( 2021 , 9 , 4 ) to return anything greater than 4 September 2021.

[TheoC]

@Jordanokstate, one way to achieve it is by adding "FILTER" to the "ALL" function.  For example: 

Measure with Date Filter =
     CALCULATE ( [Measure] ,
          FILTER ( ALL ( Table ) , Table [Date] > DATE ( 2021 , 9 , 4 ) )
                       ) 

Hope this helps mate!

Mate, modified the measure based on seeing your screenshot.  Did not think to ask about the 9/5/2021 lol.  The DATE value should be DATE ( 2021 , 9 , 4 ) to return anything greater than 4 September 2021.

[TheoC]

Hi @Anonymous 

I may not entirely understand what you are asking, so my apologies!  However, what you may be looking for is something to ignore filters.  If you have one table, then use measure one. If you are cross analysing two tables, use measure two.

1. One Table Filter = CALCULATE ( [MEASURE] , ALL ( Table1 ) )
2. Two Table Filter = CALCULATE ( [MEASURE] , ALL ( Table1 ) , ALL (Table2) )

Let me know if this helps 🙂

[Anonymous]

This works to get the values into the table as shown below, but I still do not want the table to show any rows,prior to 9/5/2021. It currently shows it even though there is a filter on the visual becaose of the ALL on the measure. How would I get rid of those dates again?

[TheoC]

@Anonymous, one way to achieve it is by adding "FILTER" to the "ALL" function.  For example: 

Measure with Date Filter =
     CALCULATE ( [Measure] ,
          FILTER ( ALL ( Table ) , Table [Date] > DATE ( 2021 , 9 , 4 ) )
                       ) 

Hope this helps mate! 🙂

Mate, modified the measure based on seeing your screenshot.  Did not think to ask about the 9/5/2021 lol.  The DATE value should be DATE ( 2021 , 9 , 4 ) to return anything greater than 4 September 2021.

[Anonymous]

Maybe I'm doing something wrong. Here is the screenshot, it now returns 0% which isn't what I am looking for. Below you can find the the power bi file, specifically I am looking at the predicted_30d_buy_from 3d measure.

https://1drv.ms/u/s!Ag08lU4J4vcXsnwAIgpVzyIUfxum?e=VLayPJ

[TheoC · Accepted solution]

Hi @Anonymous 

If you're satisfied that your original measure is correct (i.e. 10.84%), and all you are trying to do is remove the blanks, I'd recommend doing this:

By doing the above, your output will be as follows:

Although I did look at the PBIX, with my limited level of acumen, it's hard to determine all of the measures and their calculations versus what you are trying to achieve with each.

Hope the above helps mate.

[Anonymous]

OFMG THERE ARE NO WORDS TO EXPRESS HOW MUCH I APPRECIATE YOU. Midterms coming up with this expected to be due in 24 hours for work with the last couple nights I've been haiving little to no sleep -- thank you. If I can send you a 10 dollar target giftcard -- 

[TheoC]

Haha - @Anonymous do not be silly big fella! No need for a gift card or anything like that at all! I just wish I could have given you what you were looking for sooner lol!

Best of luck with it all mate! You've put together a massive amount of effort in the file. Best of luck with it all (and make sure to remove the sharing of the PBIX otherwise someone else down the track will try take it for their assignment haha).