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 are in the rows of the matrix by making selections in the slicer.

I want to make the grand total at the very bottom of the matrix correspond to the first selected value for 'Add Rows'.

Here's what I have so far:

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]) // how can i make a version of this variable that returns the FIRST selected column?

Var Total = SUMX(VALUES(SelectedRow),[Ext. GP Impact Rate Archive]) // this does NOT work because VALUES will NOT take SelectedRow as an input

// Var Total = SUMX(VALUES(Region[EDMCS_LOB_DESC]),[Ext. GP Impact Rate Archive]) // here is an example of how it works when it is NOT dynamic - this works

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

Thanks in advance for any help!

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

9 Replies

gmsamborn
Super User
1 year ago
Hi Anonymous , bhanu_gautam

EQN is a variable and is only calculated once - when it is defined.

CALCULATE(EQN) will simply return the value for EQN since it can''t be recalculated.
bhanu_gautam
Super User
1 year ago
Anonymous , Try using

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(FirstSelectedRow), [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
  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
    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
v-dineshya
Community Support
1 year ago
Hi Anonymous ,

Thanks for reaching out to the Microsoft fabric community forum.

DAX code:

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 SelectedRows = VALUES('Add Rows:'[View by: Fields])
VAR FirstSelectedRow = MINX(SelectedRows, 'Add Rows:'[View by: Fields])

VAR Total =
SWITCH(
TRUE(),
FirstSelectedRow = "GPH Level1", CALCULATE(EQN, GPH[GPH Level1]),
FirstSelectedRow = "GPH Level2", CALCULATE(EQN, GPH[GPH Level2]),
FirstSelectedRow = "GPH Level3", CALCULATE(EQN, GPH[GPH Level3]),
FirstSelectedRow = "GPH Level4", CALCULATE(EQN, GPH[GPH Level4]),
FirstSelectedRow = "Manufacturer", CALCULATE(EQN, Manufacturer[VENDOR_NAME]),
FirstSelectedRow = "Linebuy", CALCULATE(EQN, Linebuy[LINEBUY_ID]),
FirstSelectedRow = "Business Group", CALCULATE(EQN, Transactions[BUSINESS_GROUP]),
FirstSelectedRow = "PE Brand Type", CALCULATE(EQN, Transactions[PE_BRAND_TYPE]),
FirstSelectedRow = "Region", CALCULATE(EQN, Region[EDMCS_LOB_DESC]),
FirstSelectedRow = "PS Type", CALCULATE(EQN, PS_Type[PS_Type]),
Total
)

RETURN
IF(
HASONEVALUE('Add Rows:'[View by: Fields]),
CALCULATE(EQN),
Total
)

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards
- v-dineshya
  Community Support
  1 year ago
  Hi Anonymous ,
  
  If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
  Thanks and Regards
- v-dineshya
  Community Support
  1 year ago
  Hi Anonymous ,
  
  If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
  Thanks and Regards
  - v-dineshya
    Community Support
    1 year ago
    Hi Anonymous ,
    
    If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
    Thanks and Regards

Forum Discussion

Dynamic DAX

9 Replies

Related content

Dynamic Headers in DAX

Dynamic DAX Variable

Dynamic Filter

Dynamic parameter - DAX

Dynamic RLS