Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Dynamic DAX

Hi,   I have a measure in Power BI that I am trying to make dynamic. I have a matrix with a field parameter called "Add Rows" in the Rows section of the matrix. The user can select which fields...
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    Anonymous , Check this I have updated the Total

    dax
    Measure =
    VAR CurrentGP = ROUND([Ext. GP%], 10)
    VAR PreviousGP = ROUND([Ext. GP% PY], 10)
    VAR Sales = ROUND([Ext. Sales], 0)
    VAR EQN = (CurrentGP - PreviousGP) * Sales
    VAR SelectedRow = SELECTEDVALUE('Add Rows:'[View by: Fields])
    VAR FirstSelectedRow = SELECTCOLUMNS(TOPN(1, VALUES('Add Rows:'[View by: Fields])), "FirstRow", [View by: Fields])
    VAR Total = SUMX(VALUES('Add Rows:'[View by: Fields]), [Ext. GP Impact Rate Archive])
    VAR Calc =
    SWITCH(
    TRUE(),
    ISINSCOPE(GPH[GPH Level1]), CALCULATE(EQN),
    ISINSCOPE(GPH[GPH Level2]), CALCULATE(EQN),
    ISINSCOPE(GPH[GPH Level3]), CALCULATE(EQN),
    ISINSCOPE(GPH[GPH Level4]), CALCULATE(EQN),
    ISINSCOPE(Manufacturer[VENDOR_NAME]), CALCULATE(EQN),
    ISINSCOPE(Linebuy[LINEBUY_ID]), CALCULATE(EQN),
    ISINSCOPE(Transactions[BUSINESS_GROUP]), CALCULATE(EQN),
    ISINSCOPE(Transactions[PE_BRAND_TYPE]), CALCULATE(EQN),
    ISINSCOPE(Region[EDMCS_LOB_DESC]), CALCULATE(EQN),
    ISINSCOPE(Region[EDMCS_REGION_NAME]), CALCULATE(EQN),
    ISINSCOPE(Linebuy[Commodity.Category1]), CALCULATE(EQN),
    ISINSCOPE(PS_Type[PS_Type]), CALCULATE(EQN),
    ISINSCOPE(Transactions[BUSINESS_GROUP]), CALCULATE(EQN),
    ISINSCOPE(Transactions[Commodity Tie Out Product Type]), CALCULATE(EQN),
    Total
    )
    RETURN
    Calc

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi,

I get the error:
The VALUES function expects a column reference expression or a table reference expression for argument '1'.

I believe the issue is in the Total Variable.

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

Anonymous , Check this I have updated the Total

dax
Measure =
VAR CurrentGP = ROUND([Ext. GP%], 10)
VAR PreviousGP = ROUND([Ext. GP% PY], 10)
VAR Sales = ROUND([Ext. Sales], 0)
VAR EQN = (CurrentGP - PreviousGP) * Sales
VAR SelectedRow = SELECTEDVALUE('Add Rows:'[View by: Fields])
VAR FirstSelectedRow = SELECTCOLUMNS(TOPN(1, VALUES('Add Rows:'[View by: Fields])), "FirstRow", [View by: Fields])
VAR Total = SUMX(VALUES('Add Rows:'[View by: Fields]), [Ext. GP Impact Rate Archive])
VAR Calc =
SWITCH(
TRUE(),
ISINSCOPE(GPH[GPH Level1]), CALCULATE(EQN),
ISINSCOPE(GPH[GPH Level2]), CALCULATE(EQN),
ISINSCOPE(GPH[GPH Level3]), CALCULATE(EQN),
ISINSCOPE(GPH[GPH Level4]), CALCULATE(EQN),
ISINSCOPE(Manufacturer[VENDOR_NAME]), CALCULATE(EQN),
ISINSCOPE(Linebuy[LINEBUY_ID]), CALCULATE(EQN),
ISINSCOPE(Transactions[BUSINESS_GROUP]), CALCULATE(EQN),
ISINSCOPE(Transactions[PE_BRAND_TYPE]), CALCULATE(EQN),
ISINSCOPE(Region[EDMCS_LOB_DESC]), CALCULATE(EQN),
ISINSCOPE(Region[EDMCS_REGION_NAME]), CALCULATE(EQN),
ISINSCOPE(Linebuy[Commodity.Category1]), CALCULATE(EQN),
ISINSCOPE(PS_Type[PS_Type]), CALCULATE(EQN),
ISINSCOPE(Transactions[BUSINESS_GROUP]), CALCULATE(EQN),
ISINSCOPE(Transactions[Commodity Tie Out Product Type]), CALCULATE(EQN),
Total
)
RETURN
Calc

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    The total displayed at the bottom of the matrix is different when 

    VAR Total = SUMX(VALUES('Add Rows:'[View by: Fields]), [Ext. GP Impact Rate Archive])
    vs 
    Var Total = SUMX(VALUES(PS_Type[PS_Type]),[Ext. GP Impact Rate Archive]).
    Despite the fact that I only have selected PS_Type from the field parameter.
    The 2nd option above displayed the value that I want. How can I modify the first option to make it match the 2nd option while also being dynamic?

    Do you need to see [Ext. GP Impact Rate Archive] ?