Slicer/Dax Query

[andyfoster]

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

[v-jiewu-msft]

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]

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

[v-jiewu-msft]

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.

[andyfoster]

Hi,

I have done this with 

IsWithinLast6Months =

IF (

    'v_headacc_meetings_10-30'[meeting_date_months_diff] >= -6 &&

    'v_headacc_meetings_10-30'[meeting_date_months_diff] <= 0,

    "Yes",

    "No"

)

And yes it 'works' for what it is intended for, but does not solve my issue

We don't want to be able to filter BETWEEN the two, I need to be able to fully exclude accounts from the table view which have had a meeting held in the last 6 months

All the filter slicer does is remove the one row (of three) which shows a Held meeting.

Let me show this visually:

This is the starting point/view

This shows one row is a meeting within the last 6 months a sshown by the 'Yes' in the last column from a calculated column created.

When NO is applied to the slicer, this removes the row which showed a meeting held in the last 6 months, the table below the slicer still shows the account. 

By selecting YES, this only includes the row where the meeting is within the last 6 months

I understand this is the CORRECT behaviour, however this is not what we are trying to acheive. 

When selecting NO what sales want is for any account that has had a Held meeting in the last 6 months not to be visible AT ALL, not even showing meetings outside of the 6 months. 

They want to be able to see ALL accounts which have NOT had a call within the last 6 months, and I had hoped I could do this using slicers or another way. 

This is why I have been trying all sorts of calculated columns and Dax measures but do not seem to be able to find a solution

I don't necessarily need this to be in a Slicer if there is a better way using buttons or something else. 

Hope this makes sense

Thanks

[v-jiewu-msft]

Hi @andyfoster ,

May be try to create the following measure.

Filter the metting = IF(SELECTEDVALUE('v_headacc_meetings_10-30'[MeetingFlagLast6Months]) = "No", 1, 0)

Then, drag the measure to the table visual Filters pane. Set the show items is 1 and apply.

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 · Accepted solution]

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