Forum Discussion

andyfoster's avatar
andyfoster
Helper I
1 year ago
Solved

Slicer/Dax Query

Hi All, 

 

Issue:

 

I am struggling with a slicer working the way we need it to. We have a meeting_date column and the report is to display accounts without a meeting in the last 6 months within a table. 

 

i.e:

 

You can see the last column shows a yes/no which is for the meeting_date being in the last 6 months, this is the same flag beind user in the slicer.

 

Whilst this works, when i put it in to a slicer which feeds a table, what selecting 'No' does is remove the meeting which is Held within the last 6 months, and only displays older held meeetings, if I select Yes it does the opposite, by not selecting No or Yes all meetings are shown.

 

The logic my manager wants is to only show accounts which have NOT had a meeting in the last 6 months

 

Two ways I hae tried to approach this:

 

1. SQL - A quick case statement of:

 

CASE
WHEN CONVERT(DATE, meetings.Meeting_Date) >= DATEADD(MONTH, -6, GETDATE()) AND Meetings.Meeting_Status_Reporting = 'Held' THEN 'Yes' ELSE 'No'
END AS Meeting_Flag_6Months,

 

2. I created a Dax of:

IsWithinLast6Months =
IF (
    'v_headacc_meetings_10-30'[meeting_date_months_diff] >= -6 &&
    'v_headacc_meetings_10-30'[meeting_date_months_diff] <= 0,
    "Yes",
    "No"
)
Both do the same task when applied to a slicer and work exactly the same way, which is fine, and I know the logic powerbi is applying is not wrong when using the slicer, as in, it is either showing the meeting within or outside of the 6 months as per the SQL column or Dax I use, but it is not the behaviour we desire and I wonder if anyone had any bright idea's.
 
In summary:
 
We want to only show data when there has not been a meeting held on the account in the last 6 months, but the slicer is only "excuding" the view of the meeting within the last 6 months (by selecting No on the slicer)
 
This means my sales guys are seeing accounts in Powerbi showing no meetings in the last 6 months, but when they use our CRM system, they can obviously see the meeting logged in the last 6 months
 
Hope this makes sense

Many Thanks

Need Help
Tips and Tricks
  • andyfoster's avatar
    andyfoster
    1 year ago

    Hi Wisdom Wu

     

    That outcome is still not what we are trying to achieve

     

    Let's close this post, no-one has been able to offer a solution but thank you for trying. 

     

    Kindest

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi andyfoster ,

    Based on the knowledge, the slicer can not remove rows in power bi desktop. The slicer simply filters the data inside the table.

    Besides, you can try to order the metting date column in power query editor.

    Remove the top rows.

    Slicers in Power BI - Power BI | Microsoft Learn

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • andyfoster's avatar
      andyfoster
      Helper I
      1 year ago

      Hi, 

       

      Thank you for your reply

       

      The goal is not to remove any dates/data, only to use a slicer/equivilant button/feature to exclude accounts/meetings in a table where a condition is met (Meeting Held in the last 6 months), then when this condition is not met, the list of accounts appears, which then gives Sales a list of accounts to contact which have not been contacted in the last 6 months. 

       

      It's a tricky one which I haven't figured out, and given there have been 92 views on this already it seems a difficult task. 

       

      Thanks

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi andyfoster ,

        Create the new column to filter the metting date.

        MeetingFlagLast6Months = 
IF (
    'v_headacc_meetings_10-30'[Metting Date] >= TODAY () - 180,
    "Yes",
    "No"
)

        Drag the column to the slicer visual.

        Best Regards,

        Wisdom Wu

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.