Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Slicer not interacting with graph

I have a graph that displays how I want (I can change the dates in a slicer and it adjusts accordingly):     When I add a measure that I created, the graph seems to want to display the o...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    *SOLVED*
    BI Community, Thank you all for your time/support in trying to get this figured out; I could not have figured this out without the collective brainpower that exists in this community so I say it again, thank you all I really appreciate your thoughts/insights/tutelage.  Here is the solution that I came across to accomplish what I was after.

    First as suggested by holodan95 , I did need to have a separate date table with a one to many relationship to my data table.  After doing some digging, I ran across this article Cumulative sum in Power BI: CALCULATE, FILTER and ALL | by Samuele Conti | Medium which explains the madness 🙂 behind 'calculate', 'filter', and 'all' within cumulative formulas.  I apologize I am not sure where I found the 'DATEDIFF' info but as you can see in my DAX I utilize that to determine the number of weeks between two dates.  Lastly, I had two separate tables (without established relationships to each other or the data table) that I was using to pass a user selected value into a variable to perform a calculation.  Below is the finished DAX and a pic of the result.

    zzTestProjectedReduction = 
--DEC Ps115:1
VAR minDate = MIN('date_calendar'[WeekEnding])
VAR maxDate = MAX('data_table'[weekEndingExpire])
VAR noWeeks = DATEDIFF(minDate, maxDate, WEEK) + 1
VAR noEmps = SELECTEDVALUE('znoEmpTable'[Value])
VAR compTar = SELECTEDVALUE('zweeklyCompTarValues'[Value])

Return
CALCULATE(
    [zzProjectedExpTotCum] - ((noEmps * compTar) * noWeeks)
)

    The projected reduction value is the teal/cyan color:

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

holodan95 ,

 

Thanks for taking a look.  What I'm trying to accomplish is graphing my projected reduction only for the weeks selected in the slicer / shown in the graph.  So it should look something like this:

 

I was able to accomplish this before I had to introduce a separate date table however my cumulative formula for my expirations was not calculating correctly:

 

The DAX for my original projected reduction looked like this:

zTestProjectedReduction = 
VAR minDate = CALCULATE(MIN('met_mac (2)'[weekEndingExpire]),ALLSELECTED('met_mac (2)'[weekEndingExpire]))
VAR maxDate = MAX('met_mac (2)'[weekEndingExpire])
VAR noWeeks = DATEDIFF(minDate, maxDate, WEEK) + 1
VAR noEmps = SELECTEDVALUE('znoEmpTable'[Value])
VAR compTar = SELECTEDVALUE('zweeklyCompTarValues'[Value])

Return
CALCULATE(
    [zProjectedExpTotSum] - ((noEmps * compTar) * noWeeks),
    'met_mac (2)'[weekEndingExpire] <=maxDate
)

 

So I thought that if I just changed the original table references to my new date table it'd work the same but it doesn't.  Hopefully this additional information is helpful.  Thanks again for taking the time to look / respond.