Forum Discussion

danielboi's avatar
danielboi
Helper I
5 years ago
Solved

Avoiding filtered context with Variable. Help needed

Hello everybody,   making good progress with my Power Bi and now in fine tuning mode. I am building a financial report which is based on a download from our corporate data warehouse. (Very simplifi...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    danielboi , In the above I see one change , if that can work for you

     

    VAR DateMax =
    CALCULATE(
    MAXX(
    filter(allselected(FactFIRE),
    FactFIRE[Version ID]=2)    ,
    FactFIRE[Period ID]
    )
    )
    RETURN

amitchandak's avatar
amitchandak
Super User
5 years ago

danielboi , I shared on formula based on today, where eomonth move month up and down,

 

same way maxx(allselected('Table'), 'Table'[Date]) , can replace today. 

 

YTD till today =

var _today = maxx(allselected('order'), 'Table'[order]) 
var _min = date(year(_today),1,1)
var _max = eomonth(_today, 0)
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max ) )

 

In this case and today's case, you should have date selected or use all on the date table 

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

danielboi's avatar
danielboi
Helper I
5 years ago

Hello amitchandak,

thanks.  Highly appreciated your effort. My question is not so much going into the direction how to achieve to show certain numbers, but more fundamental. How to avoid, that my attempt to filter for the max date of actual version is not going to pass on a filter context only showing actual numbers.

 

Possible that my entire approach is not very efficient, but I want to go live with V1 next week and this seems to be the remaining hurdle to increase the user experience (or rather avoid user confusion)

 

By replacing the MaxDate at the very bottom with a hard coded date I get exactly what I want. So I assume that my way of extracting the date is wrong or I need to add something to the final calculate formula to eliminate the filter context generated with my variable

 

All Values Cons MaxDate =
  VAR DateMax =
    CALCULATE(
      MAXX(
         FactFIRE,
         FactFIRE[Period ID]
      ),
      FactFIRE[Version ID]=2
   )
   RETURN

 

CALCULATE(
   [All Values Uncons],
   FILTER(
      FactFIRE,
         not(
            CONTAINS(
               DimHierarchy,
               DimHierarchy[CPOC],
               FactFIRE[PCPOC]
            )
         )
      ),
      FactFIRE[Period ID]<=DateMax
)+0

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    danielboi , In the above I see one change , if that can work for you

     

    VAR DateMax =
    CALCULATE(
    MAXX(
    filter(allselected(FactFIRE),
    FactFIRE[Version ID]=2)    ,
    FactFIRE[Period ID]
    )
    )
    RETURN

    • danielboi's avatar
      danielboi
      Helper I
      5 years ago

      Yesssssss!!!!!! You did it.

       

      This did the trick.

       

      Meanwhile I also found a solution to create a separate table with the needed date, but I like this much better 🙂