topic Re: Union and iterate in DAX Commands and Tips

Union and iterate

Silvard — Wed, 29 Jan 2025 00:56:02 GMT

I’m trying to work out how to use an iterator over a dynamic table/column.

I have a fields parameter table to select the dimension to use in the visual (this contains 30 dimensions) and just a normal slicer for the measure (hence using selectedvalue).

The below is the structure that I’m trying to use, but there seems to be an issue using union as when I remove it and only use one column, I get the row with the maximum value rather than the total maximum value that I otherwise seem to get.

The Filter function is working correctly (confirmed in DAX Query) and generates the relevant rows.

VAR CombinedTable = FILTER(UNION(
                        SELECTCOLUMNS(                               VALUES('TOPN (Group)'[Group]),"DynamicValue",'TOPN (Group)'[Group],"Name","Group"),
                        SELECTCOLUMNS(                                 VALUES('TOPN (Job Family)'[Job Family]),"DynamicValue",'TOPN (Job Family)'[Job Family],"Name","Job Family")                           
                                ),
                            [Name] = MAX('Dimension'[Dimension Slicer]) 
)

VAR Result = MAXX(CombinedTable,SWITCH(SELECTEDVALUE('Measure Slicer'[Slicer]),
                         "Amount", [Amount],
                         "Net", [Net]
                        )
                    )

Return 
Result

Re: Union and iterate

Sahir_Maharaj — Wed, 29 Jan 2025 07:50:41 GMT

Hello Silvard,

Can you please try this updated approach:

VAR CombinedTable = FILTER( UNION( SELECTCOLUMNS(VALUES('TOPN (Group)'[Group]), "DynamicValue", 'TOPN (Group)'[Group], "Name", "Group"), SELECTCOLUMNS(VALUES('TOPN (Job Family)'[Job Family]), "DynamicValue", 'TOPN (Job Family)'[Job Family], "Name", "Job Family") ), [Name] = SELECTEDVALUE('Dimension'[Dimension Slicer]) ) VAR Result = MAXX( CombinedTable, SWITCH( TRUE(), SELECTEDVALUE('Measure Slicer'[Slicer]) = "Amount", [Amount], SELECTEDVALUE('Measure Slicer'[Slicer]) = "Net", [Net] ) ) RETURN Result

Re: Union and iterate

Silvard — Wed, 29 Jan 2025 08:21:20 GMT

Hi Sahir_Maharaj

Thank you for assisting.

This doesn't work unfortunately.

The issue doesn't lie in using MAX to get the value selected (I've confirmed this is working), neither is the issue that in my initial post, I had named the job family column as group rather than job family (although you have correctly corrected this- thank you).

I believe the issue lies in the utilisation of Union - if you remove Union and the subsequent below, it correctly iterates over the table but as is, whether using minx or maxx, I get the same result. To me it seems like the row context is missing? I'm fairly new to power bi so still trying to learn.

SELECTCOLUMNS(VALUES('TOPN (Job Family)'[Job Family]), "DynamicValue", 'TOPN (Job Family)'[Job Family], "Name", "Job Family")
        )

Essentially, I would like to be able to select any column in my model and apply iteration over that specific column but it's proving very challenging as Iterators and table functions like Addcolumns,selectcolumns don't accept switch or if statements.

I would greatly appreciate your continued assistance.