Show records if a condition is fulfilled at least in one slicer selection

[soldous]

Hi all

I have a table with persons and their allocation in each calendar week:

person1	week1	0.8
person1	week2	1
person1	week3	1
.....person1	.....week52	...
person2	week1	1.2
person2	week2	1
person2	week3	1
...person2	...week52	...
....	...	...

I want to show in table only persons who have at least in one week allocation more than 100% (more than 1). For weeks I have a slicer where you can choose one, more or all weeks. 

So I'm looking for a dax measure which can I add as a table filter.

I went through two solutions but neither one solved my problem.

1) calculate average allocation in all selected weeks and if it's more than 100% show values - doesn't work for more than one week

2) calculate a separate measure of each week and then a measure for filtering 

IF(WEEK1>0,1,IF(WEKK2>0,1,.....))

 It doesn't respect only selected weeks in the slicer.

Is there any DAX function or solution which can help me?

Many thanks

[soldous]

@Mariusz @Anonymous 

Thank you very much guys for the response.

Unfortunately, neither solution works for me. I attached a print screen which could help understand what I need.

In the bottom matrix, I have a summary of allocation per weeks. Value is a simple SUM(Allocation) - because one person can have more records in the same week = more project the person is allocated to. In the table above the matrix, I would like to see only persons with the allocation above 100%. So if I select all the weeks I would like to see only the red person. If I select 1st week and any other I would like to see only the red person. If I select anything except 1week I would like to see nothing.

[Anonymous · Accepted solution]

Just change the criteria in the FILTER in my formula. It was not clear at all where the value you wanted to filter by resides or if that's a measure or a column in your table.

Something like this:

[Filtering Measure] =
var __onePersonVisible = hasonevalue( Persons[PersonId] )
var __shouldBeSelected =
	not isempty(
		filter(
			values( Weeks[Week] ),
			[Total Allocation] > 1
		)
	)
var __result =
	convert(__shouldBeSelected * __onePersonVisible, boolean)
return
	__result

Best
D

[soldous]

That's it! 

Thank you very much!

[Anonymous]

This measure returns TRUE if the person should be selected and FALSE otherwise.

[Filtering Measure] =
var __onePersonVisible = hasonevalue( Persons[PersonId] )
var __shouldBeSelected =
	not isempty(
		filter(
			Persons,
			Persons[Value] > 1
		)
	)
var __result =
	__shouldBeSelected * __onePersonVisible
return
	convert(__result, boolean)

Best

D

[Mariusz]

Hi @soldous 

Try this Measure

Measure = 
SUMX( 
    'Table', 
    INT( 'Table'[value] > 1 ) 
)

Use as Visual filter like below.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn