Forum Discussion

DuncanYeah's avatar
DuncanYeah
Helper III
1 year ago
Solved

Current Selection Display

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 m...
Need Help
Show and Tell
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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

rajendraongole1's avatar
rajendraongole1
Super User
1 year ago

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