Calling SelectedValue from Slicer visual into DAX

[jason91]

Hi all,

I am trying to create a variable in DAX, with the selected value based on the slicer selection (single).

I have read articles, that I am able to call the selected value from a slicer using SELECTEDVALUE().

Here's my situation:

1. Created a table 'Report Period Selection' with one column [Report Period]
   • The table contains only 12 values, 1,2,3,....12 representing 12 months
   • I have created a measure as below:
     • Selected Period = selectedvalue('Report Period Selection'[Report Period])
   • This measure would reflect correctly my selected period on a card visualisation, e.g. slicer selection on period 8, card would reflect 8.
2. My issue arises when I place the same formula into my DAX as a variable:
   • VAR Report_Period = selectedvalue('Report Period Selection'[Report Period])
   • It would result a 0 value being called into the DAX, I have tested using 
     • VAR Report_Period = selectedvalue('Report Period Selection'[Report Period]) + 1
     • and I would obtain results for period 1
3. I have further date formulas that would rely on this Report_Period variable, which is being manually updated now, but I  would like for users to self help and toggle the report according to their reference date.

Is there anything wrong that I am doing here? Please let me know if I should post more info or if this is not possible in DAX.

Thank you!

[jason91]

Hi @OwenAuger,

The selectedvalue is calling a table with fixed values of 1 to 12.

However the table (CM/YTD Selection) which I am trying to "filter" does contain a calculation dependent on which period it is (current manually entered as a variable), to populate the table.

I have also tried referencing the variable to a measure with the selectedvalue, however the same blank error is churned out.

**EDIT: PMed you the dropbox link for the sample PBIX.

Thanks for your prompt assistance!

[OwenAuger]

Hi @jason91 

By any chance are you using SELECTEDVALUE(...) to get the selected period within a calculated column? Calculated columns cannot respond to filters from slicers or other filters in the report itself as they are processed in an unfiltered filter context independent of the report. In a calculated column, the SELECTEDVALUE expression would therefore return BLANK() and be treated as zero in calculations.

Any logic relating to the selected value of 'Report Period Selection'[Report Period] would have to be handled within measures.

If you are already using it within a measure, then the only thing I could think of is that the interactions between visuals may not be set up correctly, and the slicer may not be filtering some visuals. You can check that on Format > Edit Interactions.

Could you post a sample PBIX demonstrating the issue?

Regards,

Owen

[jason91]

Adding dropbox link for anyone else who is interested in this problem as well:

https://www.dropbox.com/s/dzjlwb090zujnbs/Sample%20Selection.pbix?dl=0

[OwenAuger]

Hi again @jason91 

Thanks for your PBIX file 🙂

I've had a look - the issue is that a calculated table (i.e. one created with DAX) cannot respond to the selection on a slicer, same as a calculated column. Calculated tables & columns are processed in an unfiltered filter context as part of creating the dataset ("before" the report is generated), so are independent of any filters applied in the report. SELECTEDVALUE(...) will return blank in this case.

The logic within the CM/YTD Selection table that depends on Report Period would need to be captured in one or more measures.

From what I can see of you CM/YTD Selection table, you most likely will want to create:

• A simple parameter table with the 3 values you had in the Selection column
• A Date table covering a sufficiently large range of dates, which can be based on dates appearing in your fact tables(s).
• Measures (or calculation groups) that take the Report Period selection along with the CM/YTD selection, and apply appropriate logic, such as filtering or blanking out results.

That's a general idea anyway. If you had an example of how you want the final report to look based on different selections, I could try to give a more specific answer.

All the best,

Owen