Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
@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] ) )
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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] ) )
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |