Scenario: Different Data Type values in the same Column
Suppose we have a dataset where the column Metric_Value contains values of different data types for each Metric_Name, as shown below.
let
SampleData =
#table(
{"Metric_Id", "Metric_Name", "Metric_Value", "Metric_DataType"},
{
{1, "ProductName", "Apple", "Text"}, {2, "ProductName", "Banana", "Text"},
{3, "UnitsSold", 150, "Number"}, {4, "UnitsSold", 275, "Number"},
{5, "GrowthRate", 0.12, "Percent"}, {6, "GrowthRate", 0.18, "Percent"},
{7, "OrderDate", #date(2023, 5, 10), "Date"},
{8, "OrderDate", #date(2024, 1, 15), "Date"},
{9, "OrderDateTime", #datetime(2023, 5, 10, 14, 30, 00), "DateTime"},
{10, "OrderDateTime", #datetime(2024, 1, 15, 9, 45, 30), "DateTime"},
{11, "OrderTime", #time(9, 45, 15), "Time"},
{12, "OrderTime", #time(21, 30, 10), "Time"},
{13, "IsActive", true, "Logical"}, {14, "IsActive", false, "Logical"}
}
),
ChangedTypes = Table.TransformColumnTypes(
SampleData,
{
{"Metric_Id", Int64.Type}, {"Metric_Name", type text},
{"Metric_Value", type any}, {"Metric_DataType", type text}
}
)
in
ChangedTypes
Displaying Max Value of each Metric on a Clustered Column Chart:
A clustered column chart in Power BI requires numeric or aggregated values on the Y‑axis in order to generate the visualization. When working with metrics that contain mixed data types, only those values that can be converted into numbers or aggregated meaningfully can be plotted on the chart.
In our scenario, the column Metric_Value contains values of different data types (such as Text, Date, and Time) that cannot be directly converted into numbers. However, these values can still be displayed on a clustered column chart as explained below.
Step1: Create a Measure to Calculate and Format Max Value of each Metric
The below measure will be used to calculate the Max Value of each Metric and format Values to the required display format:Metric Max Value =
SWITCH(
SELECTEDVALUE(tbl_Metrics_DataTypeMix[Metric_Name]),
"ProductName", MAX(tbl_Metrics_DataTypeMix[Metric_Value]),
"UnitsSold",
MAXX(
tbl_Metrics_DataTypeMix,
VALUE(tbl_Metrics_DataTypeMix[Metric_Value])
),
"GrowthRate",
FORMAT(
MAXX(
tbl_Metrics_DataTypeMix,
VALUE(tbl_Metrics_DataTypeMix[Metric_Value])
),
"0.00%"
),
"OrderDate",
FORMAT(
MAXX(
tbl_Metrics_DataTypeMix,
DATEVALUE(tbl_Metrics_DataTypeMix[Metric_Value])
),
"dd-MM-yyyy"
),
"OrderDateTime",
FORMAT(
MAXX(
tbl_Metrics_DataTypeMix,
VALUE(tbl_Metrics_DataTypeMix[Metric_Value])
),
"dd-MM-yyyy HH:mm:ss"
),
"OrderTime",
FORMAT(
MAXX(
tbl_Metrics_DataTypeMix,
TIMEVALUE(tbl_Metrics_DataTypeMix[Metric_Value])
),
"HH:mm:ss"
),
"IsActive",
FORMAT(
MAXX(
tbl_Metrics_DataTypeMix,
SWITCH(
TRUE(),
tbl_Metrics_DataTypeMix[Metric_Value] = "true", 1,
tbl_Metrics_DataTypeMix[Metric_Value] = "false", 0,
BLANK()
)
),
"General Number"
),
BLANK()
)
Step2: Create a Clustered Column Chart to show Max Value of each Metric
Create a clustered column chart with Metric_Name on the X‑axis and count of Metric_Name on the Y‑axis. 
Since the purpose is not to display the count, hide the Y‑axis values, the Y‑axis title, and the tooltips.
Next, in the Data Labels > Values section of the chart formatting options, assign the measure Metric Max Value (created earlier) so that the actual metric values are displayed on columns instead of the count.
We can use the below measure to format the Columns of the Chart.
Metric ColorFormat =
SWITCH(
SELECTEDVALUE(tbl_Metrics_DataTypeMix[Metric_Name]),
"ProductName", "#1E90FF", -- Azure Blue
"UnitsSold", "#4682B4", -- SteelBlue
"GrowthRate", "#3CB371", -- MediumSeaGreen
"OrderDate", "#FF8C00", -- DarkOrange
"OrderDateTime", "#6A5ACD", -- SlateBlue
"OrderTime", "#008080", -- Teal
"IsActive", "#228B22", -- ForestGreen
"#808080" -- Gray (default color)
)
Result:
The final output is a clustered column chart with Metric_Name on the X‑axis and metric Max values shown as data labels. The Y‑axis values, title, and tooltips are hidden so only the relevant metric values are visible.
The final output is a clustered column chart with Metric_Name on the X‑axis and metric Max values shown as data labels. The Y‑axis values, title, and tooltips are hidden so only the relevant metric values are visible.
Notes:
For text columns, the maximum values will be determined based on alphabetical order. This means the chart will display the last value in sort sequence rather than a numeric maximum.
--------------------------------------------------------------------------------------------------------
Thanks
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. If you wish, please share your genuine Feedback or comments only related to this Blog Posts. It is my humble request that, please do not post any Spam comments or Advertising kind of comments, which will be Ignored.