Current Selection Display

[DuncanYeah]

Hi all,

Dummy dataset:

Cat.	Money
A	10
B	20
C	30
D	50
E	100

In the slicer of Money, I selected from 10 to 78. But the current selection showed 10 to 50.
How to make it to show from 10 to 78.

I know why the measure return this result and I cannot build a table that from minimum money to maximum money as the table will to big which affect the performance.

The current selection measure:

concatenatex_cpe =

 var mi = FORMAT(CALCULATE(MIN('table'[Money])),"#0.0")

 var ma = FORMAT(CALCULATE(MAX('table'[Money])),"#0.0")

 return

 if(NOT ISFILTERED('table'[Money]),"","[Money ($M)]: From"&UNICHAR(10)& mi&UNICHAR(10)&"to"&UNICHAR(10)&ma&" ; "&UNICHAR(10)&"")

Best Regard,
Duncan

[DuncanYeah]

Thanks you @MNedix ,@rajendraongole1 & @Kedar_Pande .
Given that I'm working in direct query mode, creating an additional table using DAX isn't feasible. The real data spans from 0 to well over 3 billion values and beyond.

Any suggestions on how to navigate this situation would be greatly appreciated.

Thank You.

Best Regard,
Duncan

[rajendraongole1 · Accepted solution]

Hi @DuncanYeah - DirectQuery limitations mean that creating new tables dynamically is not feasible, but you can still use ALLSELECTED to capture slicer selections that might go beyond existing data values.Pre-aggregating your data into manageable ranges or buckets could provide a performance boost and make querying over large value ranges more efficient.
Using dynamic slicer filters or ranges could help limit the selection scope while keeping the solution flexible.

You can use the following logic to display the range the user selected via the slicer, without filtering the actual data:

concatenatex_cpe =
VAR mi = FORMAT(MINX(ALLSELECTED('table'[Money]), 'table'[Money]), "#0.0")
VAR ma = FORMAT(MAXX(ALLSELECTED('table'[Money]), 'table'[Money]), "#0.0")
RETURN
IF(
NOT ISFILTERED('table'[Money]),
"",
"[Money ($M)]: From " & UNICHAR(10) & mi & UNICHAR(10) & " to " & UNICHAR(10) & ma & " ; " & UNICHAR(10)
)

you can consider using a relative filter or range slicer based on aggregation. This can ensure the user can filter within practical ranges without loading too many rows or overburdening the system. You could implement a dynamic range selection by creating bins or intervals at the database level, which Power BI can then handle more efficiently in DirectQuery mode.

Hope this works and helps in your scenerio

[MNedix]

Heya,

I don't really understand what you're trying to achieve but at least for the first topic you should create an additional table with the below formula:

Series = GENERATESERIES(0,100,1)

Then, connect the Value of this new table to the Money column in your table. Then use the Value as a filter in your visual (see below)

PS: the relationship is 1-to-1 because you have unique values in both tables. However, I assume in your real data you have repeating values in the Table so the relationship will change to Many-to-1.

[rajendraongole1]

Hi @DuncanYeah - Create a new table in Power BI for the money range disconnected to main one.

calculated table as below:

MoneyRange = GENERATESERIES(0, 100, 1)

Now Update your measure to refer to the slicer’s selected values (from MoneyRange), not the actual data table values.

concatenatex_cpe =
VAR mi = FORMAT(MINX(ALLSELECTED(MoneyRange), MoneyRange[Value]), "#0.0")
VAR ma = FORMAT(MAXX(ALLSELECTED(MoneyRange), MoneyRange[Value]), "#0.0")
RETURN
IF(
NOT ISFILTERED(MoneyRange[Value]),
"",
"[Money ($M)]: From" & UNICHAR(10) & mi & UNICHAR(10) & "to" & UNICHAR(10) & ma & " ; " & UNICHAR(10)
)

Now, your measure will reflect the full range selected in the slicer, regardless of whether there are actual data points for every value in that range.

Hope this works in your scenerio

[Kedar_Pande]

modified measure:

concatenatex_cpe =
VAR mi = FORMAT(MIN('table'[Money]), "#0.0") // Minimum value selected in the slicer
VAR ma = 
IF(
ISFILTERED('table'[Money]), 
FORMAT(MAX('table'[Money]), "#0.0"), 
"78" // Set this to your desired max range, e.g., 78
)
RETURN
IF(
NOT ISFILTERED('table'[Money]), 
"", 
"[Money ($M)]: From" & UNICHAR(10) & 
mi & UNICHAR(10) & 
"to" & UNICHAR(10) & 
ma & " ; " & UNICHAR(10) & ""
)

If you need the maximum value to be dynamic based on other slicers or conditions, consider creating a separate parameter table to define maximum values.

If this helped, a Kudos 👍 or a Solution mark would be awesome! 🎉
Cheers,
Kedar Pande
Connect on LinkedIn