Load the values of a different column based on a filter (ie: dynamic column)

[olimilo]

I have a custom table Selection with the following values in the [Options] column: "Country", "Region", "City"; and a calculated column 'Data'[Selection] whose values will depend on which value the user selects from the 'Selection'[Options] column.

If I select "Country", it would populate the calculated column with the values from Data[Country]; select "Region" and populate it with Data[Region] values and so on.

So far, all that I have seen would require me to pivot my table. Is there a way to not do a pivot? Basically, what I would like to do is similar to the one below (doesn't work):

Selection = 
    IF(HASONEVALUE('Selection'[Options]),
        SWITCH(
            VALUES('Selection'[Options]),
            "Country", [Country]
            "Region", [Region]
            "City", [City]
        )
    )

[v-piga-msft]

Hi @olimilo ,

More details will be much helpful.

Do you have more than one table or only the table?

If it is convenient, could you share the data sample and your desired output so that we could have a test on it?

Best  Regards,

Cherry

[olimilo]

Hi @v-piga-msft 

This is what the sample data looks like:

The two tables are not related. What will happen is, the Selection table will be used for a chiclet/slicer. When the user selects a value (eg: Region), it will show a matrix table containing the calculated column and a measure showing the number of work orders by region. If they select Country, it will show the work orders by country instead; and if they select City, show work orders by city.

So basically, the calculated column is dynamic based on which item the user selects in the slicer (Selection[Option]), ie: it's just substituting which column to show depending on the user's selection.

[v-piga-msft]

Hi @olimilo ,

Have you solved your problem?

If you have solved the problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[v-piga-msft · Accepted solution]

Hi @olimilo ,

By my tests and research, we cannot achieve your desired output directly with measures.

If you do not want to unpivot your original Data Table, you could create a calculated table as a  workaroud.

Table =
UNION (
    SELECTCOLUMNS (
        'Data Table',
        "orderid", [Work OrderID],
        "type", "country",
        "value", [Country]
    ),
    SELECTCOLUMNS (
        'Data Table',
        "orderid", [Work OrderID],
        "type", "Region",
        "value", [Region]
    ),
    SELECTCOLUMNS (
        'Data Table',
        "orderid", [Work OrderID],
        "type", "city",
        "value", [City]
    )
)

Here is the output.

More details, you could refer to my attachment.

Best Regards,

Cherry