cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Switching columns dynamically without unpivoting to display a line chart

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.

 

PowerQuestions_0-1634461193951.png

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?

 

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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] ) )
        )
    )

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
Fowmy
Super User
Super User

@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] ) )
        )
    )

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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

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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors