Dynamic DAX

[fergu513]

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!

[v-dineshya]

Hi @fergu513 ,

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]

Hi @fergu513 ,

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]

Hi @fergu513 ,

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]

Hi @fergu513 ,

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

[gmsamborn]

Hi @fergu513 , @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]

@fergu513 , 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

[fergu513]

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 · Accepted solution]

@fergu513 , 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

[fergu513]

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] ?