Excel table filters not recognised in PowerPivot measures

0
votes

I'm trying to create a parameter table to change the units the figures in my pivot table are presented in. I have:

  • TransTBL table with my transactions.
  • Units table with 1; 1,000; 1,000,000 in column A and Units; K's; M's in column B.

Both tables are in my data model.

I added the Units Column B as a slicer to TransTBL, despite the fact they have no connections.

I then tried to create a measure in the Units table on the PowerPivot side to recognise which slicer option was chosen. My measure won't recognise the slicer selection. However it will recognise the filter if I apply it directly to the table in PowerPivot.

I'm using the HASONEVALUE function as described here.

1
excelpowerpivotdax

1 Answers

0
votes

The measure works fine as expected. 

AmountDisplay = 
IF(
    HASONEVALUE(Units[B]),
    SUM(TransTBL[Amount]) / VALUES(Units[A]),
    SUM(TransTBL[Amount])
)

result

Can you provide more details on your data or model to see if there are any specific problems associated?