Forum Discussion

Silvard's avatar
Silvard
Icon for Resolver I rankResolver I
1 year ago

Union and iterate

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

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    1 year ago

    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
    • Silvard's avatar
      Silvard
      Icon for Resolver I rankResolver I
      1 year ago

      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.