Forum Discussion

gbrunnerbi's avatar
gbrunnerbi
Advocate I
6 years ago
Solved

DAX function / filter context issue

Hi,   I need some help with filter contexts and dax formulas.   My setup is this: I have a EndQuarter - ENDDATE - selector (slicer) (e.g. 9/30/2019) - which filters all values in my data table -...
Need Help
  • gbrunnerbi's avatar
    gbrunnerbi
    6 years ago

    I was just able to actually solve this:

     

    -I created a new table (basically duplicating one of my queries)

    -Added a column calculating the values that I need (you have to adapt it since it is not a measure of course, like removing aggregators and replacing them with the actual columns in your table)

     

    Now I created a measure which should be displayed in my visual.

    e.g. CALCULATE ( SUM ('table'[Value))

    I used REMOVEFILTERS to get rid of the slicer selection (this selection forced me to stay within the quarter which the user selected)

    And now I could use DATESBETWEEN('Dates'[Date] , FIRSTDATE('Table'[Date]) , LASTDATE('Dates'[Date] )

    to actually get the time period that I wanted to look at, in my visual.

     

    Now whatever quarter the user selects will be the last data point on my visual.

     

    If someone has a similar problem and my solution is not clear, please pm me

v-zhenbw-msft's avatar
v-zhenbw-msft
Community Support
6 years ago

Hi gbrunnerbi ,

 

We can use the following steps to meet your requirement.

 

1. The line chart date based on table date, but the slicer date based on date table date.

 

There is no relationship between table and date table.

 

 

2. Then we can create a measure to get the line chart value before Endofmonth. The result like this,

 

Measure = IF(SELECTEDVALUE(Dates[Date]),CALCULATE(SUM('Table'[result]),FILTER('Table','Table'[date]<=SELECTEDVALUE(Dates[Date]))),CALCULATE(SUM('Table'[result])))

 

 

 

If it doesn’t meet your requirement, could you please provide a mockup sample  based on fake data or describe the fields of each tables and the relations between tables simply?

 

It will be helpful if you can show us the exact expected result based on the tables.

 

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

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

  • gbrunnerbi's avatar
    gbrunnerbi
    Advocate I
    6 years ago

    I need to keep the filters in place (they are filtering my data table)

    AND

    I cannot use a disconnected table but rather have to solve it with a DAX formula.

     

    It is not that straight forward...