Multiple Variables in DAX

[villa1980]

Hi all,

 Not sure if this can be achieved but I have 3 metrics, sales, margin and qty. Currently these are seperate metrics and I was wondering if these can be in one metric using variables?

Thanks

Alex

[grazitti_sapna · Accepted solution]

Hi @villa1980,

Yes, you can combine calculations for sales, margin, and quantity into a single measure using variables. However, keep in mind that a measure in DAX always returns a single scalar value, so you'll need to decide whether you want to return one of the metrics based on a condition (such as a slicer selection) or concatenate them into a text string.

For example, if you want the user to choose which metric to display using a slicer, you could create a measure like this:

Combined Metric =
VAR SalesMetric = [Sales]
VAR MarginMetric = [Margin]
VAR QtyMetric = [Qty]
VAR SelectedMetric = SELECTEDVALUE('Metric Selector'[Metric]) // a table with options "Sales", "Margin", "Qty"
RETURN
SWITCH(
SelectedMetric,
"Sales", SalesMetric,
"Margin", MarginMetric,
"Qty", QtyMetric,
SalesMetric // default to Sales if nothing is selected
)

Alternatively, if you want to display all three metrics in one card visual as a concatenated string, you could do something like:

Combined Metric Text =
VAR SalesMetric = FORMAT([Sales], "#,##0")
VAR MarginMetric = FORMAT([Margin], "0.00%")
VAR QtyMetric = FORMAT([Qty], "#,##0")
RETURN
"Sales: " & SalesMetric & " | Margin: " & MarginMetric & " | Qty: " & QtyMetric

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[danextian · Accepted solution]

Hi @villa1980 

That's possible but you would need  another table containing a column that holds the metric names,  a sort column and a format string.  Just variables without a row that you can assign the values to won't work.

In the measure below, you can use the actual expressions but I prefer putting them in their own measures for re-usability.

My Metrics =
VAR _Rev = [Total Revenue]
VAR _TXn = [Total Transactions]
VAR _ATR = [Average Rev]
RETURN
    SWITCH (
        SELECTEDVALUE ( Metrics[Metric] ),
        "Revenue", _Rev,
        "Transactions", _TXn,
        "ATR", _ATR
    )

The Format String column in the Metrics table will be used to apply the desired formatting to each metric. The above screnshot shows #,#.00 instead of different format for each. While you can use FORMAT function, that will return a text string instead of a number.

If this is the only thing you're trying to achieve, I would just use Field Parameters intead which is easier to setup and automated. You can access this feature from the Modeling tab, New Parameter then Fields. This creates a calculated table which you can edit if you need to rename the Display Names, chang the sort, add and or delete a measure.

Please see the attached sample pbix.

[grazitti_sapna · Accepted solution]

Hi @villa1980,

Yes, you can combine calculations for sales, margin, and quantity into a single measure using variables. However, keep in mind that a measure in DAX always returns a single scalar value, so you'll need to decide whether you want to return one of the metrics based on a condition (such as a slicer selection) or concatenate them into a text string.

For example, if you want the user to choose which metric to display using a slicer, you could create a measure like this:

Combined Metric =
VAR SalesMetric = [Sales]
VAR MarginMetric = [Margin]
VAR QtyMetric = [Qty]
VAR SelectedMetric = SELECTEDVALUE('Metric Selector'[Metric]) // a table with options "Sales", "Margin", "Qty"
RETURN
SWITCH(
SelectedMetric,
"Sales", SalesMetric,
"Margin", MarginMetric,
"Qty", QtyMetric,
SalesMetric // default to Sales if nothing is selected
)

Alternatively, if you want to display all three metrics in one card visual as a concatenated string, you could do something like:

Combined Metric Text =
VAR SalesMetric = FORMAT([Sales], "#,##0")
VAR MarginMetric = FORMAT([Margin], "0.00%")
VAR QtyMetric = FORMAT([Qty], "#,##0")
RETURN
"Sales: " & SalesMetric & " | Margin: " & MarginMetric & " | Qty: " & QtyMetric

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!