topic Re: Slicer selection not Working in a Virtual Table in DAX Commands and Tips

Slicer selection not Working in a Virtual Table

jimmyhua — Sat, 16 Nov 2024 11:25:07 GMT

I created a virtual table with rankings. then I created a real table called TopN using Generateseries to create a list of int numbers and made a slicer. last I create a variable called SelectedN .
the TopN Table = GENERATESERIES(5,30,5)

then i created a VAR SelectedN = SELECTEDVALUE(TopNTable[Value]),
Last I added the VAR to the filter to the virtual table called RankedTable. however, it gives me blank.

RETURN

FILTER(

RankedTable,

[RankByBL] <= SelectedN)

then I checked the slicer in the visual using card the selection was fine. last I replace SelectedN with hardcode 15 or 10, the table filtered correctly. I also tried to changed SELECTEDVALUE(TopNTable[Value], Max(TopNTable[Value])), the SelectedN will pickup the default Max number which is 30 in this case, even if I have the slicer at 15. So SELECTEDVALUE(TopNTable[Value]) is giving me empty result. why it behaive like that? Thanks for helping.

Re: Slicer selection not Working in a Virtual Table

lbendlin — Sat, 16 Nov 2024 13:40:45 GMT

You cannot meaningfully create calculated tables from measures or slicers. Only works the other way round.

Re: Slicer selection not Working in a Virtual Table

Anonymous — Mon, 18 Nov 2024 02:27:17 GMT

Thanks for the reply from lbendlin.

Hi jimmyhua ,

The SELECTEDVALUE function returns the value when the context for columnName has been filtered down to a single distinct value; otherwise, it returns an alternative value. If no alternative value is provided and the slicer is not filtered, SELECTEDVALUE returns null when there is no single distinct value in the context.

If the TopNTable you create has only one value, then you don't need to use a slicer.

Best Regards,
Zhu
Community Support Team

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

Re: Slicer selection not Working in a Virtual Table

Poojara_D12 — Mon, 18 Nov 2024 11:13:03 GMT

Hi jimmyhua

The behavior you're observing is due to filter context and how the SELECTEDVALUE function works in DAX. Let's analyze the situation and resolve the issue step by step:

Why SelectedN Returns Blank

The SELECTEDVALUE function depends on the filter context applied to the column. When you use a slicer to filter the TopN Table, the selection is applied to that specific column. However, if you reference SELECTEDVALUE(TopNTable[Value]) in a different table's measure (e.g., your virtual RankedTable), the filter context from the slicer may not propagate to that measure or visual.

This is why SelectedN is blank in some scenarios, but when you hardcode a value like 15 or 10, the filter works as expected.

Solution

To fix this, ensure the slicer's filter context is explicitly passed to your calculation. Here’s how:

1. Define SelectedN Safely

Use MAX instead of SELECTEDVALUE, which ensures that the slicer's selection is retrieved correctly, even if used outside the slicer's visual context:

VAR SelectedN = MAX(TopNTable[Value])

MAX works because the slicer limits the TopNTable[Value] column to a single value, making it equivalent to SELECTEDVALUE in this case.

2. Apply Filter to RankedTable

Use the SelectedN variable directly in your filter condition:

RETURN FILTER( RankedTable, [RankByBL] <= SelectedN )

Alternative Debugging Step

If SelectedN is still blank, verify that the slicer is affecting the filter context. Create a debugging measure to display the current value of SelectedN:

Debug SelectedN = MAX(TopNTable[Value])

Add this measure to a card visual to confirm the slicer selection. If this returns blank:

Check the relationships between the TopN Table and other tables. A missing or inactive relationship can prevent the slicer's selection from propagating.
Verify that the slicer has valid options (e.g., numbers between 5 and 30) and isn't clearing filters.

Ensuring Slicer Works with the Visual

To ensure that the slicer's selection dynamically filters the RankedTable, follow these steps:

Confirm that TopNTable[Value] is directly used in the slicer.
Verify that the TopN Table exists in the data model and isn't only a disconnected table. If it’s disconnected, you may need to adjust the logic or use a measure like this:

SelectedN = CALCULATE( MAX(TopNTable[Value]), ALLSELECTED(TopNTable) )

Ensure the slicer is set to Single Select mode for proper behavior.

Final Measure Example
Here’s your final measure incorporating the solutions:

Ranked Table Filter = VAR SelectedN = MAX(TopNTable[Value]) // Ensure selection is retrieved RETURN FILTER( RankedTable, [RankByBL] <= SelectedN )

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

Re: Slicer selection not Working in a Virtual Table

jimmyhua — Tue, 19 Nov 2024 23:08:07 GMT

Hi PooJara,
Thank you for the detail response. yes. the table would react to MAX(TopNTable[Value]), however, since it gives the largest selection, how it can help me to use it when I want to select a lower value?

Re: Slicer selection not Working in a Virtual Table

Anonymous — Thu, 21 Nov 2024 05:35:04 GMT

Thanks for the reply from Poojara_D12.

Hi jimmyhua ,

Please use the following DAX formula:

Ranked Table Filter = VAR SelectedN = IF(HASONEVALUE('TopN Table'[Value]),MAX('TopN Table'[Value]),MIN('TopN Table'[Value])) RETURN FILTER( 'RankedTable', [Rank] <= SelectedN )

Result:

Best Regards,
Zhu
Community Support Team

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

topic Re: Slicer selection not Working in a Virtual Table in DAX Commands and Tips

Slicer selection not Working in a Virtual Table

Re: Slicer selection not Working in a Virtual Table

Re: Slicer selection not Working in a Virtual Table

Re: Slicer selection not Working in a Virtual Table

Why SelectedN Returns Blank

Solution

1. Define SelectedN Safely

2. Apply Filter to RankedTable

Alternative Debugging Step

Ensuring Slicer Works with the Visual

Final Measure Example

Re: Slicer selection not Working in a Virtual Table

Re: Slicer selection not Working in a Virtual Table