Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.