Forum Discussion

SahityaYeruband's avatar
SahityaYeruband
Icon for Helper II rankHelper II
1 year ago
Solved

Slicer Selection to show/hide column values & corresponding counts

Hi Everyone,

I am required to build a matrix visual containing, for eg :  Country, Dept, Product, Sales.
Slicers are given on Month & year of sales.

Each Product has a introdution date. Sales are captured from the date of introduction. However the sales shouldn't be shown unless its after a month of Product Introduction. 

CountryCategoryProductIntroduction DateSlicer Selected by User 
USABeautyFoundation23/05/202410-2024Should show in Visual
USAHair CareBlow Dryer08/08/20248-2024Shouldn't show in Visual
USAHair CareBlow Dryer08/08/20249-2024Should Show in Visual
USABeautyFoundation23/05/20244-2024Shouldn't show in the Visual


Appreciate your help in advance.

 

Thanks,

Sahitya Y

  • ValtteriN's avatar
    ValtteriN
    1 year ago

    Hi,

    I am not sure if I follow, but here is how to show selection based on slicer.

    Measure to count = 

    COUNTROWS('Table (48)')

    test to show values =
    var _slicerD = MAX(DimDate[Date]) RETURN
    IF(MAX('Table (48)'[Introduction Date])+30< _slicerD,1,0)

    Apply test as a filter:

     

    Now when September is selected all values are shown since 30 days after 8.8 is larger than 30.9. The critical date is 7.9.2024 since 30+8.8 < 7.9. 

    So if 7.9 is selected the products introduced at 8.8 are not shown:

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/



3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    SahityaYeruband , First Create a Calculated Column for Introduction Month

    IntroductionMonthYear = FORMAT([Introduction Date], "YYYY-MM")

     

    Create a Measure to Check if Sales Should be Shown:

    ShowSales =
    VAR SelectedMonthYear = SELECTEDVALUE('SlicerTable'[MonthYear])
    VAR IntroductionMonthYear = FORMAT([Introduction Date], "YYYY-MM")
    VAR IntroductionPlusOneMonth = FORMAT(EDATE([Introduction Date], 1), "YYYY-MM")
    RETURN
    IF(SelectedMonthYear >= IntroductionPlusOneMonth, 1, 0)

     

    Use the measure ShowSales to filter the matrix visual. Only show rows where ShowSales is 1.

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    1 year ago

    Hi,

    I am not sure if I follow, but here is how to show selection based on slicer.

    Measure to count = 

    COUNTROWS('Table (48)')

    test to show values =
    var _slicerD = MAX(DimDate[Date]) RETURN
    IF(MAX('Table (48)'[Introduction Date])+30< _slicerD,1,0)

    Apply test as a filter:

     

    Now when September is selected all values are shown since 30 days after 8.8 is larger than 30.9. The critical date is 7.9.2024 since 30+8.8 < 7.9. 

    So if 7.9 is selected the products introduced at 8.8 are not shown:

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/



  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago

    SahityaYeruband 

    Create a measure that checks the condition and filters data dynamically:

    Show Sales =
    IF(
    DATEDIFF(
    MIN('Table'[Introduction Date]),
    EOMONTH(SELECTEDVALUE('Date'[Date]), 0),
    MONTH
    ) >= 1,
    SUM('Table'[Sales]),
    BLANK()
    )


    Use Show Sales as the value in the matrix visual.
    Add slicers for month and year to filter data dynamically.

     

    💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
    Best regards,
    Kedar
    🌐 Connect on LinkedIn