How to create a dynamic Measure/Function to dynamically scale line and bar charts

[eliasayyy]

Hello Everyone!

I am doing data analytics for marketing campaign.

I want to display on my line chart multiple measures based on Field parameter measure selected. some examples of measures.

Impressions, Clicks and Spend.

I want to see my performance over time and impressions is way bigger than the other 2 that only impressions show a nice curve while the other selected measure will be close to 0 and no curve or story telling at all.

Is there a dynamic measure i can make or using dax query to create a function that auto scales based on the max selected measure without having to create a series of measures based o neach scenario? so if i only have clicks, spend and conversion, spend is the max neasure we scale based on spend. if clicks max measure then we scale based on clicks... 

thank you in advanced

[amitchandak · Accepted solution]

@eliasayyy , if field parameter is an Axis, then you can switch measures based on selected Axis 

Change Set of Measures based on Axis: Field Parameters- https://youtu.be/7ikRAelDph0

Another option UDF 

Power BI DAX UDF — User Defined Function- https://medium.com/@amitchandak/power-bi-dax-udf-user-defined-function-044df4ab67ac

[v-hjannapu · Accepted solution]

Hi @eliasayyy,

Thank you  for reaching out to the Microsoft fabric community forum.
This is happening because Impressions has very big values compared to Clicks and Spend, so Power BI automatically sets the Y-axis based on Impressions and the other two look almost flat. Right now, Power BI does not support auto-scaling the Y-axis based on the measure selected through Field Parameters, and there is no DAX that can dynamically control the chart axis like that. The only practical options are to use a secondary Y-axis for smaller measures, keep separate charts for high and low value metrics, or convert the values into percentages or index format so the trends are visible.

Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Regards,
Community Support Team.

[v-hjannapu]

Hi @eliasayyy,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Community Support Team.

[v-hjannapu]

Hi @eliasayyy,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Community Support Team.

[BugmanJ]

I use a dynamic parameter which is linked to my date calander. Therefore I can easily switch my chart to switch between days for something small, all the way to months or even years for greater numbers. If you need to keep the time/dates as one unit, ie days, you can make a seperate column pre-generated in Power Query (you can run a calculated column too with some logic like last 7 days is 1, 7-21 days is 2 and so on, so each parameter selection is to a number to which it filters the table and hence your chart to.

[amitchandak · Accepted solution]

@eliasayyy , if field parameter is an Axis, then you can switch measures based on selected Axis 

Change Set of Measures based on Axis: Field Parameters- https://youtu.be/7ikRAelDph0

Another option UDF 

Power BI DAX UDF — User Defined Function- https://medium.com/@amitchandak/power-bi-dax-udf-user-defined-function-044df4ab67ac

[danextian]

Hi @eliasayyy 

You can try the following measure:

MAXX ( { [clicks measure], [spend measure], [converstion measure] }, [Value] )

This creates a single colum (default column name is Value) virtual table containing the values of those measures. MAXX will then pick the max value from that column.