topic Re: Dynamic DAX in DAX Commands and Tips

Dynamic DAX

Anonymous — Fri, 04 Apr 2025 12:33:46 GMT

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!

Re: Dynamic DAX

bhanu_gautam — Fri, 04 Apr 2025 12:48:58 GMT

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

Re: Dynamic DAX

Anonymous — Fri, 04 Apr 2025 14:00:39 GMT

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.

Re: Dynamic DAX

bhanu_gautam — Fri, 04 Apr 2025 14:19:04 GMT

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

Re: Dynamic DAX

gmsamborn — Sat, 05 Apr 2025 20:24:45 GMT

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.

Re: Dynamic DAX

v-dineshya — Mon, 07 Apr 2025 12:03:40 GMT

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

Re: Dynamic DAX

Anonymous — Mon, 14 Apr 2025 14:29:44 GMT

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

Re: Dynamic DAX

v-dineshya — Tue, 15 Apr 2025 12:52:15 GMT

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

Re: Dynamic DAX

v-dineshya — Thu, 24 Apr 2025 07:16:22 GMT

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

Re: Dynamic DAX

v-dineshya — Mon, 28 Apr 2025 08:59:51 GMT

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