Table contains measures and not slicing as expected

[jimmyfromus]

Hi, 

I have a table visual that contains some measures. It has six columns, contact, company, last event, next event, pipeline phase and profit. 

Last event and next event are calculated measures. 

I created a slicer dropdown for contact but the contacts in the underlying table are also showing in the dropdown. What is the best way to get around this so only John and George would be shown in the contact slicer dropdown? Thanks for the support. 

Here's the Table. 

Contact	Session	Event	Participation	Start date	Group	Pipeline phase	Profit	Status	Company
John L	Lunch	Event 1	Yes	30.12.2020 00:00	HYL-Commerce	1-Qualify	4000	Invited	Company 1
John L	Breakfast	Event 2	Yes	31.12.2020 08:00	HYL-Commerce	1-Qualify	4000	Invited	Company 1
John L	Evening Meal	Event 3	No	31.12.2020 08:00	HYL-Commerce	1-Qualify	4000	Invited	Company 1
John L	Supper	Event 4	No	29.11.2020 08:00	HYL-Commerce	1-Qualify	4000	Invited	Company 1
John L	Brunch	Event 5	Yes	29.11.2020 08:00	HYL-Commerce	1-Qualify	4000	Invited	Company 1
George	Afternoon tea	Event 6	Yes	27.06.2020 08:00		1-Qualify		Invited	Company 2
Paul	Morning tea	Event 7	No	01.10.2020 08:00		1-Qualify		Invited	Company 3
Ringo	Snack	Event 8	No	03.12.2020 08:00				Invited	Company 4

The measure for last Event is: 

Last event =

VAR _lastpdate =

CALCULATE (

MAX ( 'Table'[Start date] ),

FILTER (

ALL ( 'Table' ),

'Table'[Contact] = MAX ( 'Table'[Contact] )

&& 'Table'[Participation] = "Yes"

)

)

VAR _lastpevent =

CALCULATE (

MAX ( 'Table'[Session] ),

FILTER (

ALL ( 'Table' ),

'Table'[Contact] = MAX ( 'Table'[Contact] )

&& 'Table'[Participation] = "Yes"

&& 'Table'[Start date] = _lastpdate

)

)

RETURN

( _lastpevent )

Thanks. 

[v-luwang-msft]

Hi @jimmyfromus   ，

Has your problem been solved, if so, please consider Accept a correct reply as the solution to help others find it.

Best Regard

Lucien Wang

[v-luwang-msft]

Hi @jimmyfromus ,

According to what you have mentioned，you want to filter for last event which is not blank, and the values wouldn't  be picked up where they are blank and get the value in the next event column, moreover you want just 3 people to appear in the dropdown, right?

If so, try the following measure:

 

Last event =

VAR _lastpdate =

CALCULATE (

MAX ( 'Table'[Start date] ),

FILTER (

ALL ( 'Table' ),

'Table'[Contact] = MAX ( 'Table'[Contact] )

&& 'Table'[Participation] = "Yes"

)

)

VAR _lastpevent =

CALCULATE (

MAX ( 'Table'[Session] ),

FILTER (

ALL ( 'Table' ),

'Table'[Contact] = MAX ( 'Table'[Contact] )

&& 'Table'[Participation] = "Yes"

&& 'Table'[Start date] = _lastpdate

)

)

RETURN

( _lastpevent )

 

 

Next event =

VAR _nextpdate =

CALCULATE (

MAX ( 'Table'[Start date] ),

FILTER (

ALL ( 'Table' ),

'Table'[Contact] = MAX ( 'Table'[Contact] )

&& 'Table'[Participation] = "No"  && 'Table'[Pipeline phase]<>BLANK()

)

)

VAR _nextpevent =

CALCULATE (

MAX ( 'Table'[Session] ),

FILTER (

ALL ( 'Table' ),

'Table'[Contact] = MAX ( 'Table'[Contact] )

&& 'Table'[Participation] = "No"

&& 'Table'[Start date] = _nextpdate &&'Table'[Pipeline phase]<>BLANK()

)

)

RETURN

( _nextpevent )

If  the value in Lastevent is blank ,then input the value in Nextevent column ,I create a measure  for finalevent is:

Final event =

VAR _lastpdate =

CALCULATE (

MAX ( 'Table'[Start date] ),

FILTER (

ALL ( 'Table' ),

'Table'[Contact] = MAX ( 'Table'[Contact] )

&& 'Table'[Participation] = "Yes"

)

)

VAR _lastpevent =

CALCULATE (

MAX ( 'Table'[Session] ),

FILTER (

ALL ( 'Table' ),

'Table'[Contact] = MAX ( 'Table'[Contact] )

&& 'Table'[Participation] = "Yes"

&& 'Table'[Start date] = _lastpdate

)

)

VAR _nextpdate =

CALCULATE (

MAX ( 'Table'[Start date] ),

FILTER (

ALL ( 'Table' ),

'Table'[Contact] = MAX ( 'Table'[Contact] )

&& 'Table'[Participation] = "No" && 'Table'[Pipeline phase]<>BLANK()

)

)

VAR _nextpevent =

CALCULATE (

MAX ( 'Table'[Session] ),

FILTER (

ALL ( 'Table' ),

'Table'[Contact] = MAX ( 'Table'[Contact] )

&& 'Table'[Participation] = "No"

&& 'Table'[Start date] = _nextpdate &&'Table'[Pipeline phase]<>BLANK()

)

)

Var finalevent=

IF(_lastpevent<>BLANK(), _lastpevent, _nextpevent)

RETURN

( finalevent)

Finally you will see:

 

 

About the configuration of the slicer:

 

 

You can take a look and if you have other questions, you are welcomed to continue to ask.

 

 

Best Regards,

Lucien

[parry2k]

@jimmyfromus you can create another measure with the logic and return 1 or 0 and use that measure in the filter.

[jimmyfromus]

I'm not too sure how to do that. Do I have to create a separate table for that?

[parry2k]

@jimmyfromus you can have a visual level filter on the slicer with one of the measure with condition where measure value is not blank

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[jimmyfromus]

@parry2k Thanks, yeah that's correct. But I'm looking to exactly reflect the table in the dropdown. If for example, I filter for last event is not blank, then it wouldn't pick up the value where it's value is blank and there is a value in the next event column. I would like to have just 3 people to appear in the dropdown list as below, is that possible? Thank you again, I value the support.