apply function for specific element DAX

[Addora]

Hello!, Please I ask fro support in applying function for specific element DAX.

I Have a column called LOB, contains 11 elements "Criteria"

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]LOB[/TD]
[/TR]
[TR]
[TD]Business[/TD]
[/TR]
[TR]
[TD]Golden Customers[/TD]
[/TR]
[TR]
[TD]NC[/TD]
[/TR]
[TR]
[TD]Reapers[/TD]
[/TR]
[TR]
[TD]TEData-Sales[/TD]
[/TR]
[TR]
[TD]TEData-Sales "Subscription"[/TD]
[/TR]
[TR]
[TD]WE[/TD]
[/TR]
[TR]
[TD]Xceed-UP Q[/TD]
[/TR]
[TR]
[TD]Xceed-Basic[/TD]
[/TR]
[TR]
[TD]Xceed-Sales[/TD]
[/TR]
</tbody>[/TABLE]

I need to create a measure called AHT, but to be specified based on LOB, e.g

For Below LOBs I need to Use measure: AHT:=(((([Sum of ACD Time]+[Sum of ACW Time]+[Sum of Hold Time])-[Sum of ACWOUTOFFTIME])/86400)/[Sum of ACD Calls])

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Business[/TD]
[/TR]
[TR]
[TD]Golden Customers[/TD]
[/TR]
[TR]
[TD]NC[/TD]
[/TR]
[TR]
[TD]Reapers[/TD]
[/TR]
[TR]
[TD]TEData-Sales[/TD]
[/TR]
[TR]
[TD]TEData-Sales "Subscription"[/TD]
[/TR]
[TR]
[TD]WE[/TD]
[/TR]
</tbody>[/TABLE]

but for below LOBs I need to apply this function to the same measure

AHT:=((([Sum of ACD Time]+[Sum of ACW Time]+[Sum of Hold Time])/86400)/[Sum of ACD Calls])

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Xceed-UP Q[/TD]
[/TR]
[TR]
[TD]Xceed-Basic[/TD]
[/TR]
[TR]
[TD]Xceed-Sales[/TD]
[/TR]
</tbody>[/TABLE]

 

[FranzV]

You can solve this using a Lookup Table and a Calculated Column. Try the following steps:

• Add a table to your data model with LOB values that require the first measure.
  • I will call it FirstMeasureCriteria and it only has one column: LOB.
• Add a calculated column to your original table called RequiresFirstMeasure with the formula:
  • = CONTAINS( FirstMeasureCriteria , FirstMeasureCriteria[LOB] , YourTable[LOB] )
  • This will return TRUE if the LOB in the current row context can be found in the lookup table.
• Create one measure for each different calculation and name them FirstAHT and SecondAHT.
• Create the AHT measure that chooses between the two:
  • AHT:= IF( VALUES( YourTable[RequiresFirstMeasure] ) , [FirstAHC] , [SecondAHC] )

I hope it helps.