Forum Discussion

eliasayyy's avatar
eliasayyy
Icon for Memorable Member rankMemorable Member
3 years ago
Solved

create a dynamic selection slicer that affects date slicer

hello everyone i have a normal calendar table and another table called selection table

 

 

i made a measure

Filter Latest Month For SP = 
SWITCH(
    SELECTEDVALUE('Period Select'[ID]),
    1, IF( AND(MAX('Calendar'[Week Number]) = WEEKNUM(TODAY(),2),MAX('Calendar'[Year Number]) = YEAR(TODAY())),1,0),
    2, IF( AND(MAX('Calendar'[Month Number]) = MONTH(TODAY()),MAX('Calendar'[Year Number]) = YEAR(TODAY())),1,0),
    3, IF( AND(MAX('Calendar'[Quarter Number]) = QUARTER(TODAY()),MAX('Calendar'[Year Number]) = YEAR(TODAY())),1,0),
    4, IF(MAX('Calendar'[Year Number]) = YEAR(TODAY()) , 1 , 0),
    5, IF(MAX('Calendar'[Year Number]) = YEAR(TODAY()) -1 , 1 , 0),
    6,1
)

 

 

i added this measure to my 2 slicers , year slicer and timeline slicer , and set it to is 1



when i select the main slicer selection it works 

Examples:



but it doesnt affect my measures or does a filter to my visuals

it should say from june 19 to june 23 but it shows me my whole data



 

 

 

  • eliasayyy's avatar
    eliasayyy
    3 years ago

    ok i just created a new table

    Test Dynamic Table = 
UNION(
    SELECTCOLUMNS(
        FILTER('Calendar', 'Calendar'[Date] >= TODAY() - WEEKDAY(TODAY(), 2) + 1 && 'Calendar'[Date] <= TODAY()),
        "Date", 'Calendar'[Date],
        "Type", "Current Week",
        "Order", 1
    ),
    SELECTCOLUMNS(
        FILTER('Calendar', 'Calendar'[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) && 'Calendar'[Date] <= TODAY()),
        "Date", 'Calendar'[Date],
        "Type", "Current Month",
        "Order", 2
    ),
    SELECTCOLUMNS(
        FILTER('Calendar', 'Calendar'[Date] >= DATE(YEAR(TODAY()), INT((MONTH(TODAY()) - 1) / 3) * 3 + 1, 1) && 'Calendar'[Date] <= TODAY()),
        "Date", 'Calendar'[Date],
        "Type", "Current Quarter",
        "Order", 3
    ),
    SELECTCOLUMNS(
        FILTER('Calendar', 'Calendar'[Date] >= DATE(YEAR(TODAY()), 1, 1) && 'Calendar'[Date] <= TODAY()),
        "Date", 'Calendar'[Date],
        "Type", "Current Year",
        "Order", 4
    ),
    SELECTCOLUMNS(
        FILTER('Calendar', 'Calendar'[Date] >= DATE(YEAR(TODAY()) - 1, 1, 1) && 'Calendar'[Date] <= DATE(YEAR(TODAY()) - 1, 12, 31)),
        "Date", 'Calendar'[Date],
        "Type", "Last Year",
        "Order", 5
    ),
    SELECTCOLUMNS(
        FILTER('Calendar', 'Calendar'[Date] >= MIN('Calendar'[Date]) && 'Calendar'[Date] <= MAX('Calendar'[Date])),
        "Date", 'Calendar'[Date],
        "Type", "Custom",
        "Order", 6
    )
)


    made a relationship between my new table and calendar table from "Date" Column and szet it to both and it worked

1 Reply

  • eliasayyy's avatar
    eliasayyy
    Icon for Memorable Member rankMemorable Member
    3 years ago

    ok i just created a new table

    Test Dynamic Table = 
UNION(
    SELECTCOLUMNS(
        FILTER('Calendar', 'Calendar'[Date] >= TODAY() - WEEKDAY(TODAY(), 2) + 1 && 'Calendar'[Date] <= TODAY()),
        "Date", 'Calendar'[Date],
        "Type", "Current Week",
        "Order", 1
    ),
    SELECTCOLUMNS(
        FILTER('Calendar', 'Calendar'[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) && 'Calendar'[Date] <= TODAY()),
        "Date", 'Calendar'[Date],
        "Type", "Current Month",
        "Order", 2
    ),
    SELECTCOLUMNS(
        FILTER('Calendar', 'Calendar'[Date] >= DATE(YEAR(TODAY()), INT((MONTH(TODAY()) - 1) / 3) * 3 + 1, 1) && 'Calendar'[Date] <= TODAY()),
        "Date", 'Calendar'[Date],
        "Type", "Current Quarter",
        "Order", 3
    ),
    SELECTCOLUMNS(
        FILTER('Calendar', 'Calendar'[Date] >= DATE(YEAR(TODAY()), 1, 1) && 'Calendar'[Date] <= TODAY()),
        "Date", 'Calendar'[Date],
        "Type", "Current Year",
        "Order", 4
    ),
    SELECTCOLUMNS(
        FILTER('Calendar', 'Calendar'[Date] >= DATE(YEAR(TODAY()) - 1, 1, 1) && 'Calendar'[Date] <= DATE(YEAR(TODAY()) - 1, 12, 31)),
        "Date", 'Calendar'[Date],
        "Type", "Last Year",
        "Order", 5
    ),
    SELECTCOLUMNS(
        FILTER('Calendar', 'Calendar'[Date] >= MIN('Calendar'[Date]) && 'Calendar'[Date] <= MAX('Calendar'[Date])),
        "Date", 'Calendar'[Date],
        "Type", "Custom",
        "Order", 6
    )
)


    made a relationship between my new table and calendar table from "Date" Column and szet it to both and it worked