cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

DAX calculation that calls to a parameter

I have a line chart that shows sales data by fiscal week and I want to be able to display only up through the selected fiscal week. There is a parameter [fiscal week selection] that allows viewers to input what fiscal week they want to see metrics on and I want this to be the end of the range of data in my line chart. So I need a calculation that filters the line chart to show a line from fiscal week 1 through to [fiscal week selection]. I can do this range using a number with this calculation:
 
inserted =
CALCULATE (
[Sum of Opps (All opps)],
FILTER (
( 'CALENDAR' ),
'CALENDAR'[Fiscal Week]
> 0 && 'CALENDAR'[Fiscal Week] <= 20
), 'CALENDAR'[Fiscal Year]=2019
)
 
but when I try to put [fiscal week selection] in place of 20, it does not display the data correctly. I do not want to add a slicer to filter my chart as this adds yet another element viewers have to change/input. I want them to be able to choose a fiscal week and see the metrics for that week as well as have the chart change to display only up through the selected week. 
9 REPLIES 9
Super User II
Super User II

Re: DAX calculation that calls to a parameter

@Anonymous - You need to use a variable, like this:

inserted =
var selected_fiscal_week = MAX('CALENDAR'[Fiscal Week])
return CALCULATE (
[Sum of Opps (All opps)],
FILTER (
( 'CALENDAR' ),
'CALENDAR'[Fiscal Week]
> 0 && 'CALENDAR'[Fiscal Week] <= selected_fiscal_week
), 'CALENDAR'[Fiscal Year]=2019
)
I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson
Anonymous
Not applicable

Re: DAX calculation that calls to a parameter

@natelpeterson this does not filter my chart, it still shows up through the current fiscal week (35) and does not change the line when the parameter value is changed. 

Super User II
Super User II

Re: DAX calculation that calls to a parameter

@Anonymous  - What do you mean by a parameter value? Could you share a screenshot?

Thanks,

Nathan

Anonymous
Not applicable

Re: DAX calculation that calls to a parameter

@natelpetersonso there's a "What if" parameter on the page with a series generated from 1-53,  Capture.PNGCapture5.PNG

Super User II
Super User II

Re: DAX calculation that calls to a parameter

@Anonymous - The measure will need to refer to the "what if parameter":

inserted =
var selected_fiscal_week = MAX('Your What If Parameter Table'[Your What If Parameter Column])
return CALCULATE (
    [Sum of Opps (All opps)],
   FILTER (
        'CALENDAR',
        'CALENDAR'[Fiscal Week] > 0 && 'CALENDAR'[Fiscal Week] <= selected_fiscal_week
    ), 
    'CALENDAR'[Fiscal Year]=2019
)
I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson
Anonymous
Not applicable

Re: DAX calculation that calls to a parameter

@natelpeterson I have done that and it continues to show to the current week, even when I change the parameter

 

inserted2 =
var selected_fiscal_week = MAX(Parameter[Fiscal Week])
return CALCULATE (
[Sum of Opps (All opps)],
FILTER (
'CALENDAR',
'CALENDAR'[Fiscal Week] > 0 && 'CALENDAR'[Fiscal Week] <= selected_fiscal_week
),
'CALENDAR'[Fiscal Year]=2019
)
Super User II
Super User II

Re: DAX calculation that calls to a parameter

@Anonymous  - Could you share your pbix and expected result? Or screenshot of all pertinent info and expected result?

Thanks,

Nathan

Anonymous
Not applicable

Re: DAX calculation that calls to a parameter

@natelpeterson 

 

My line chart is created using these measures:

 

2018 Web Opps = CALCULATE([Sum of Opps (All opps)], FILTER('Calendar','Calendar'[Fiscal Year]=2018),OPPS[VEHICLE_RESPONSE_CODE]="Web")
 
2019 Web Opps = CALCULATE([Sum of Opps (All opps)], FILTER('Calendar','Calendar'[Fiscal Year]=2019),OPPS[VEHICLE_RESPONSE_CODE]="Web")
 
Sum of Web Opps Goals = SUM('2019 Goals Data (Web Opps)'[Web Opps Goal])
 
- All of my tables are connected to a business calendar table by business date, allowing me to also connect them by fiscal week
 
- what I need is for the 2019 Web Opps line to filter from fiscal week 1 to the selected fiscal week
 
- I have a slicer on the page that is connected to a what if parameter that goes from 1-53 and allows for the viewer to select a fiscal week to view
 
- the "inserted" measure I have above is a test to see if I can filter the Web Opps, which I have not been able to.
 
I set a variable as Parameter[Fiscal Week] which when I click on it shows this:
 
Sum of Web Opps Goals = SUM('2019 Goals Data (Web Opps)'[Web Opps Goal])
 
And I have also tried setting the variable in inserted to Parameter[fiscal Week Selection] which when I click shows the measure is calculated like this:
Fiscal Week Selection = SELECTEDVALUE('Parameter'[Fiscal Week])
 
My Parameter is also connected to my calendar table by fiscal week (1-53)
Super User II
Super User II

Re: DAX calculation that calls to a parameter

@Anonymous  - I'm not sure what else to try, except some troubleshooting steps. Your original measure works with a hard-coded max week. The measure with a variable based on MAX(Parameter[Fiscal Week]) is not working, so then I'd test the value for MAX(Parameter[Fiscal Week]) in a separate measure. If that is giving the proper value, then the only way for the measure with variable to be wrong is for relationships to be messing it up somehow. But, to troubleshoot, I'd need the pbix.

Thanks,

Nathan

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors