Forum Discussion
Slicer not interacting with graph
- Anonymous3 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:
Also, you'll have to work from the date table after connecting it to the others. Basically what you need is this connection:
TABLE1 and DATE_TABLE -->1:Many from DATE_TABLE to TABLE1
TABLE2 and DATE_TABLE -->1:Many from DATE_TABLE to TABLE2
In every measure and slicer, work with the dates from DATE_TABLE
That's weird because those two tables are only used to pass a user selected value into a variable called out in the measure. As you can see in the one graph I displayed before I separated the dates into a different table,
the graph displayed correctly (light blue line) even with the 'znoEmpTable' and 'zweeklyCompTarValues' tables not having an established relationship to any other tables. From what I can tell because they have no influence on filtering the data (just passing a number into a variable) there shouldn't need to be a relationship.
Also, both tables are single column tables with 10 values each ( 1-10 ) in one and ( 10, 20... 100) in the other, not really any way to create a relationship.
Thanks for the thoughts/time, I appreciate your efforts.