DAX Function To Detect Column Label

[SimonNU]

Hi Guys

I'm designing some metrics that change depending on what the column label is. Below is a simple example demonstrating the problem:

Column Labels = January, February, March etc...

I would like put the following into a dax function where ??????? is a function that returns the column label name:


=SWITCH(???????, "January", CALCULATE(...), "February", CALCULATE(...), "March",CALCULATE(...)Thanks!!!</pre>

 

[scottsen]

Assuming you are talking about a measure, and talking about a pivot table (as opposed to like... calculated columns and columns in a power pivot table)... then general pattern you see is...

IF (HASONEVALUE(Calendar[Month]),
IF (VALUES(Calendar[Month]) = "January...", ...

So... I guess your ????? is replaced by VALUES(Calendar[Month]), but you would want to continue to protect against multiple months in a cell (grand totals), else there will be errors and sadness.

 

[SimonNU]

I'm receiving the following error:

"Calculation error in measure 'UTILIZATION SHEETS'[99fb0c30-bad9-47aa-ae33-5d0b90626b27]: The value for column 'SOURCE' in table 'COMBINED GL DATA' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified."

Below is the actual formula. The current year metrics are pulled from 'COMBINED GL DATA' whereas anything else is pulled from 'UTILIZATION SHEETS', hence the need to detect the column label that the measure is calculating under:

=IF(VALUES('COMBINED GL DATA'[SOURCE]="CURRENT YEAR ACTUALS"),

CALCULATE(SUM('UTILIZATION SHEETS'[CS]), FILTER('UTILIZATION SHEETS', 'UTILIZATION SHEETS'[Period]=MAX('UTILIZATION SHEETS'[Period]))),

CALCULATE(SUM('COMBINED GL DATA'[CURRENT PERIOD]),'PROFIT AND LOSS DESCRIPTION MAP'[PL Description]="CS HOURS"))

 

[SimonNU]

Oops, ignore the above formula, refer to the below:

=IF(VALUES('COMBINED GL DATA'[SOURCE]="CURRENT YEAR ACTUALS"),

CALCULATE(SUM('COMBINED GL DATA'[CURRENT PERIOD]),'PROFIT AND LOSS DESCRIPTION MAP'[PL Description]="CS HOURS"),

CALCULATE(SUM('UTILIZATION SHEETS'[CS]), FILTER('UTILIZATION SHEETS', 'UTILIZATION SHEETS'[Period]=MAX('UTILIZATION SHEETS'[Period]))))

 

[SimonNU]

I've found the below solution. It ain't pretty though:

if(CALCULATE(SUM('COMBINED GL DATA'[CURRENT PERIOD]),'PROFIT AND LOSS DESCRIPTION MAP'[PL Description]="CS HOURS")=0,

CALCULATE(SUM('UTILIZATION SHEETS'[CS]), FILTER('UTILIZATION SHEETS', 'UTILIZATION SHEETS'[Period]=MAX('UTILIZATION SHEETS'[Period]))),

CALCULATE(SUM('COMBINED GL DATA'[CURRENT PERIOD]),'PROFIT AND LOSS DESCRIPTION MAP'[PL Description]="CS HOURS"))

 

[scottsen]

you would want to continue to protect against multiple months in a cell (grand totals), else there will be errors and sadness.

In your case, you want something like ...

=IF (HASONEVALUE('COMBINED GL DATA'[SOURCE]), 
    IF(VALUES('COMBINED GL DATA'[SOURCE]="CURRENT YEAR ACTUALS"),
       CALCULATE(SUM('UTILIZATION SHEETS'[CS]), FILTER('UTILIZATION SHEETS', 'UTILIZATION SHEETS'[Period]=MAX('UTILIZATION SHEETS'[Period]))),
       CALCULATE(SUM('COMBINED GL DATA'[CURRENT PERIOD]),'PROFIT AND LOSS DESCRIPTION MAP'[PL Description]="CS HOURS"))
    )
 )

 

[rwilson2014]

Hi scottsen

Have you seen his article?

DAX – Making the “Case” for SWITCH() « PowerPivotPro

I had similar problem to solve as you and this worked it for me.

Cheers