topic Re: Show records if a condition is fulfilled at least in one slicer selection in DAX Commands and Tips

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

soldous — Fri, 06 Mar 2020 11:02:40 GMT

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

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

Mariusz — Fri, 06 Mar 2020 11:35:52 GMT

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

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

Anonymous — Fri, 06 Mar 2020 11:46:42 GMT

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

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

soldous — Fri, 06 Mar 2020 12:25:47 GMT

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.

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

Anonymous — Fri, 06 Mar 2020 12:47:46 GMT

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

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

soldous — Fri, 06 Mar 2020 12:59:58 GMT

That's it!

Thank you very much!