topic Re: Switching columns dynamically without unpivoting to display a line chart in DAX Commands and Tips

Switching columns dynamically without unpivoting to display a line chart

Anonymous — Sun, 17 Oct 2021 09:15:21 GMT

Hey there,

I want to display sales data over time in a line chart.

My table looks similar to this one: The fourth column holds sales in currency code EUR, the fifth column holds it in the local currency if it is different from EUR.

I do not want to unpivot those columns in Power Query as every row would be duplicated in this process and the table becomes messy.

Addidtionally I have a slicer, where the user selects whether to show data in local currency or in EUR.

This selection should only impact the data, if a single country is selected (column1).

I want to achieve a DAX measure, that returns the whole column data - no aggregation - for displaying the data in a line chart.
Something like this:
IF(HASONEFILTER([Column1]) AND slicer[selection] = "Local Currency", [Column5], [Column4])

The problem is, that it throws an error because this expression would return a whole column, not a scalar value.

Is there a way to achieve this without unpivoting columns? Do I have to change my data structure?

Re: Switching columns dynamically without unpivoting to display a line chart

Fowmy — Sun, 17 Oct 2021 09:27:01 GMT

Anonymous

You cannot return a column in this case, you need to use an aggregator function. Use the following measure, modify the correct table and column names as per your model.

NewMeasure = VAR __Slicer = SELECTEDVALUE ( Slicer[selection] ) RETURN IF ( HASONEFILTER ( TableName[Column1] ), SWITCH ( TRUE (), __Slicer = "Local Currency", CALCULATE ( SUM ( TableName[Column5] ) ), __Slicer = "Foreign Currency", CALCULATE ( SUM ( TableName[Column4] ) ) ) )

Re: Switching columns dynamically without unpivoting to display a line chart

Anonymous — Sun, 17 Oct 2021 10:25:26 GMT

Fowmy

First, thanks for your answer.

One more question: Would I then be able to display the sales data over time? A Column with datetimes is also in the table.

If I understand correctly, I can only show aggregated values for example on a card visual.

Re: Switching columns dynamically without unpivoting to display a line chart

Fowmy — Sun, 17 Oct 2021 10:36:25 GMT

Anonymous
Yes, you can, add the date/time column in a line chart and this measure.
It should show the correct value on a card visual as well.