Forum Discussion

olimilo's avatar
olimilo
Icon for Post Prodigy rankPost 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) =...
Need Help
  • Anonymous's avatar
    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

olimilo's avatar
olimilo
Icon for Post Prodigy rankPost 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's avatar
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