Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
olimilo
Responsive Resident
Responsive Resident

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])
				)
			)
		)
	)

 

1 ACCEPTED SOLUTION

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])
				)
	)

 

Capture.PNG

 

>>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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft

 

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).

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])
				)
	)

 

Capture.PNG

 

>>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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin!

 

Sample data in here:

 

CountryTypePossible Working DaysDays WorkedOOF DaysTravel DaysUT Rate
USAR754810381%
USAC9631100%
CanadaC2200100%
MexicoR853872987%
MexicoC353230100%
MexicoO5500100%
BrazilR64378477%
BrazilC131302100%
El SalvadorC2200100%
HaitiC1101100%
PeruC1101100%

 

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.