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 no...
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.