Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
@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.
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
That's it!
Thank you very much!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |