Post Prodigy

9 years ago

Solved

IF(HASONEFILTER()) with a filter selection?

So I'm trying to convert this logic to DAX. I have three different formulas that I use to get the utilization rate of a person that depends on their position. If:

1. Contractor
Utilization (C) = (Days Worked + OOF Days) / (Days Worked + OOF Days)

2. Other
Utilization (O) = (Days Worked + Travelling Days + OOF Days) / (Days Worked + Travelling Days + OOF Days)
* Essentially for Contractor/Other, if the sum of all required values > 0, 100%, but I have to use the formula and not the IF-ELSE

3. Regular Employees (General Formula)
Utilization (R) = (Days Worked + Travelling Days + OOF Days) / Possible Working Days

Basically, it should use the general formula if only the Regular Employees filter is selected, or if all filters are selected (ie: fallback). Is it possible to use HASONEFILTER with an "equals to" value? Like:

HASONEFILTER([Type] = "Contractor")

I'm trying this, but I'm not getting the correct value. If I have all three values selected, it just gets the formula for the Other and not the general formula.

Utilization Rate = 
	IF(CONTAINS(test, [Type], "Other"),
		CALCULATE(
			DIVIDE(
				SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days]),
				SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days])
			)
		)
		,
		IF(CONTAINS(test, [Type], "Subcontractor"),
			CALCULATE(
				DIVIDE(
					SUM([Days Worked]) + SUM(test[OOF Days]),
					SUM([Days Worked]) + SUM(test[OOF Days])
				)
			)
			,
			CALCULATE(
				DIVIDE(
					SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days]),
					SUM(test[Possible Workdays Per Month])
				)
			)
		)
	)

Need Help

Anonymous

9 years ago

Hi olimilo,

You can try to use below formuals:

Utilization Rate(Calculate Column) = 
SWITCH(Sheet3[Type],
	"O",
			DIVIDE(
				[Days Worked] + Sheet3[OOF Days]+ Sheet3[Travel Days],
				[Days Worked] + Sheet3[OOF Days] + Sheet3[Travel Days]
			),
	"C",
				DIVIDE(
					[Days Worked] + Sheet3[OOF Days],
					[Days Worked] + Sheet3[OOF Days]
				),
	"R",				
				DIVIDE(
					Sheet3[Days Worked] + Sheet3[OOF Days] + Sheet3[Travel Days],
					Sheet3[Possible Working Days]
				)
	)


Utilization Rate(Measure) = 
var currtype=if(HASONEVALUE(Sheet3[Type]),VALUES(Sheet3[Type]),BLANK())
var currType2=LASTNONBLANK(Sheet3[Type],[Type])
return
SWITCH(currtype,
	"O",
			DIVIDE(
				SUM([Days Worked]) + SUM(Sheet3[OOF Days]) + SUM(Sheet3[Travel Days]),
				SUM([Days Worked]) + SUM(Sheet3[OOF Days]) + SUM(Sheet3[Travel Days])
			),
	"C",
				DIVIDE(
					SUM([Days Worked]) + SUM(Sheet3[OOF Days]),
					SUM([Days Worked]) + SUM(Sheet3[OOF Days])
				),
	"R",				
				DIVIDE(
					SUM(Sheet3[Days Worked]) + SUM(Sheet3[OOF Days]) + SUM(Sheet3[Travel Days]),
					SUM(Sheet3[Possible Working Days])
				)
	)

>>Is there something I can use to replace the MAX() as the switch case for this?

You can use lastnonblank or isonevalue to get the current row value in measure.

Regards,

Xiaoxin Sheng

4 Replies

Anonymous

Not applicable

9 years ago

Hi olimilo,

>>Basically, it should use the general formula if only the Regular Employees filter is selected, or if all filters are selected (ie: fallback). Is it possible to use HASONEFILTER with an "equals to" value? Like:
You can try to use below measure which used to get the select item from slicer:

Selected Item=IF(HASONEVALUE(Table[Column]),VALUES(Table[Column]),BLANK)

>>I'm trying this, but I'm not getting the correct value. If I have all three values selected, it just gets the formula for the Other and not the general formula.

For your requirement, you can try to use switch function with calculate column:

Calculate column:
Utilization Rate = 
SWITCH(test[Type],
	"Other",
			DIVIDE(
				SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days]),
				SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days])
			),
	"Subcontractor",
				DIVIDE(
					SUM([Days Worked]) + SUM(test[OOF Days]),
					SUM([Days Worked]) + SUM(test[OOF Days])
				),
				DIVIDE(
					SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days]),
					SUM(test[Possible Workdays Per Month])
				)
	)

Measure:
Utilization Rate = 
SWITCH(MAX(test[Type]),
	"Other",
			DIVIDE(
				SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days]),
				SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days])
			),
	"Subcontractor",
				DIVIDE(
					SUM([Days Worked]) + SUM(test[OOF Days]),
					SUM([Days Worked]) + SUM(test[OOF Days])
				),
				DIVIDE(
					SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days]),
					SUM(test[Possible Workdays Per Month])
				)
	)

If above not help, please share some sample data to test.

Regards,

Xiaoxin Sheng

olimilo

Post Prodigy

9 years ago

Hi Xiaoxin!

Sample data in here:

Country	Type	Possible Working Days	Days Worked	OOF Days	Travel Days	UT Rate
USA	R	75	48	10	3	81%
USA	C	9	6	3	1	100%
Canada	C	2	2	0	0	100%
Mexico	R	85	38	7	29	87%
Mexico	C	35	32	3	0	100%
Mexico	O	5	5	0	0	100%
Brazil	R	64	37	8	4	77%
Brazil	C	13	13	0	2	100%
El Salvador	C	2	2	0	0	100%
Haiti	C	1	1	0	1	100%
Peru	C	1	1	0	1	100%

The formula for the utilization varies according to type (R, C, O). Also, why was there a MAX() on the measure? Does that mean we're getting all three types in the filter?

olimilo

Post Prodigy

9 years ago

Hi Anonymous

Thanks for the solution. I have since reordered my data and with the given measure, it only gets the first case (just the Other) if I have all three types (Regular, Contractor, Other) selected and does not fall to the fall back formula (ie: the else case). Is there something I can use to replace the MAX() as the switch case for this?

Edit: I just checked, the MAX() only gets the last entry when you sort the 3 types, so it gets Subcontractor (since it goes last when you sort the 3 types alphabetically).

Anonymous

Not applicable

9 years ago

Hi olimilo,

You can try to use below formuals:

Utilization Rate(Calculate Column) = 
SWITCH(Sheet3[Type],
	"O",
			DIVIDE(
				[Days Worked] + Sheet3[OOF Days]+ Sheet3[Travel Days],
				[Days Worked] + Sheet3[OOF Days] + Sheet3[Travel Days]
			),
	"C",
				DIVIDE(
					[Days Worked] + Sheet3[OOF Days],
					[Days Worked] + Sheet3[OOF Days]
				),
	"R",				
				DIVIDE(
					Sheet3[Days Worked] + Sheet3[OOF Days] + Sheet3[Travel Days],
					Sheet3[Possible Working Days]
				)
	)


Utilization Rate(Measure) = 
var currtype=if(HASONEVALUE(Sheet3[Type]),VALUES(Sheet3[Type]),BLANK())
var currType2=LASTNONBLANK(Sheet3[Type],[Type])
return
SWITCH(currtype,
	"O",
			DIVIDE(
				SUM([Days Worked]) + SUM(Sheet3[OOF Days]) + SUM(Sheet3[Travel Days]),
				SUM([Days Worked]) + SUM(Sheet3[OOF Days]) + SUM(Sheet3[Travel Days])
			),
	"C",
				DIVIDE(
					SUM([Days Worked]) + SUM(Sheet3[OOF Days]),
					SUM([Days Worked]) + SUM(Sheet3[OOF Days])
				),
	"R",				
				DIVIDE(
					SUM(Sheet3[Days Worked]) + SUM(Sheet3[OOF Days]) + SUM(Sheet3[Travel Days]),
					SUM(Sheet3[Possible Working Days])
				)
	)

>>Is there something I can use to replace the MAX() as the switch case for this?

You can use lastnonblank or isonevalue to get the current row value in measure.

Regards,

Xiaoxin Sheng

Forum Discussion

IF(HASONEFILTER()) with a filter selection?

4 Replies

Related content

Dynamic Filtering based on date selection

Adding additional record as in Drop down selection/Filter Selection.

Filters not works while selecting multiple Period

Filter

Activator crashes when selecting Property Filter