Dynamic grouping of years based on slicer

[Anonymous]

Dear BI community

I have a data set like this which shows the built year of every ship in the period from 2000-2024. 

Built year	Ship
2010	A
(blank)	B
2019	BB
2022	C
2023	Dr
2024	E

I need to make a table visual that shows this, where there is a grouping of earlier years, which includes ships without data on built year:

Built year	Ships
<2023 and blank (GROUP)	4
2023	1
2024	1

BUT I also need a slicer on the page so the user can choose when the grouping starts based on built year. So e.g. the user could also set the group year to begin at 2022 (instead of 2023 as above) so it would look like this instead:

Built year	Ships
<2022 and blank (GROUP)	3
2022	1
2023	1
2024	1

I hope it makes sense and somebody can help me 🙂

[Anonymous · Accepted solution]

Hi, @Anonymous 

Thanks for lbendlin's concern about this issue.

I am glad to help you.

If you want to get a dynamic year based on Slicer, then you can only use Measure. but Measure gets a value, not an actual column, so it can't dynamically group the ships based on the obtained Year. 

Maybe you can refer to my solution: 

First you need New table for Slicer filtering: 

SlicerTable = 
GENERATESERIES ( MIN ( Table1[Built year] ), MAX ( Table1[Built year] ), 1 )

Create a Measure for grouping: 

GroupYear = 
VAR SelectedYear =
    SELECTEDVALUE ( 'SlicerTable'[Year] )
RETURN
    IF (
        ISBLANK ( MAX ( 'Table1'[Built year] ) )
            || MAX ( 'Table1'[Built year] ) < SelectedYear,
        "<" & SelectedYear & " and blank (GROUP)",
        MAX ( 'Table1'[Built year] )
    )

Create a Measure for calculating ships: 

Ships = 
VAR _Year =
    MAX ( 'Table1'[Built year] )
VAR _selectedYear =
    MAX ( 'SlicerTable'[Year] )
RETURN
    IF (
        _Year < _selectedYear,
        CALCULATE (
            COUNTROWS ( 'Table1' ),
            FILTER ( ALL ( Table1 ), [Built year] < _selectedYear )
        ),
        COUNTROWS ( 'Table1' )
    )

Result: 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Teach your users how to operate the Filter panel. They can formulate their filter choices there.