Measure for cluster chart from multiple select of multiple column

[Dwesty_A]

Hi, I want to make measure for cluster chart for y axix. I make script like this, but when i do multiple select, chart will showing blank, but if only single select chart will be show. I already try so many way but when I try multiple select they will show blank. This script I use for measure :

1

IF(SELECTEDVALUE(Pilihan[Slicer])="Penempatan Pada EPM",
SELECTCOLUMNS(EA_IBL, "Penempatan Pada EPM", 'EA_IBL'[1. 1 Penempatan Pada EPM]),
IF(SELECTEDVALUE(Pilihan[Slicer])="Surat Berharga Dalam Valas",
SELECTCOLUMNS(EA_IBL, "Surat Berharga Dalam Valas", 'EA_IBL'[1. 2 Surat Berharga Dalam Valas]),
IF(SELECTEDVALUE(Pilihan[Slicer])="Hak Tarik Khusus",
SELECTCOLUMNS(EA_IBL, "Surat Berharga Dalam Valas", 'EA_IBL'[1. 3 Hak Tarik Khusus]),
IF(SELECTEDVALUE(Pilihan[Slicer])="Deposito Dalam Valas",
SELECTCOLUMNS(EA_IBL, "Surat Berharga Dalam Valas", 'EA_IBL'[1. 4 Deposito Dalam Valas]),
IF(SELECTEDVALUE(Pilihan[Slicer])="Emas",
SELECTCOLUMNS(EA_IBL, "Surat Berharga Dalam Valas", 'EA_IBL'[1. 5 Emas]),
IF(SELECTEDVALUE(Pilihan[Slicer])="Giro Dalam Valas",
SELECTCOLUMNS(EA_IBL, "Surat Berharga Dalam Valas", 'EA_IBL'[1. 6 Giro Dalam Valas]),
IF(SELECTEDVALUE(Pilihan[Slicer])="Tagihan Lainnya Dalam Valas",
SELECTCOLUMNS(EA_IBL, "Surat Berharga Dalam Valas", 'EA_IBL'[1. 7 Tagihan Lainnya Dalam Valas])
)))))))

2

SWITCH(
TRUE(),
SELECTEDVALUE(Pilihan[Slicer])="Penempatan Pada EPM", SELECTCOLUMNS('EA_IBL',"Penempatan Pada EPM",'EA_IBL'[1. 1 Penempatan Pada EPM]),
SELECTEDVALUE(Pilihan[Slicer])="Surat Berharga Dalam Valas", SELECTCOLUMNS('EA_IBL', "Surat Berharga Dalam Valas",'EA_IBL'[1. 2 Surat Berharga Dalam Valas]),
SELECTEDVALUE(Pilihan[Slicer])="Hak Tarik Khusus", SELECTCOLUMNS('EA_IBL', "Hak Tarik Khusus", 'EA_IBL'[1. 3 Hak Tarik Khusus]),
SELECTEDVALUE(Pilihan[Slicer])="Deposito Dalam Valas", SELECTCOLUMNS('EA_IBL', "Deposito Dalam Valas",'EA_IBL'[1. 4 Deposito Dalam Valas]),
SELECTEDVALUE(Pilihan[Slicer])="Emas", SELECTCOLUMNS('EA_IBL', "Emas",'EA_IBL'[1. 5 Emas]),
SELECTEDVALUE(Pilihan[Slicer])="Giro Dalam Valas", SELECTCOLUMNS('EA_IBL', "Giro Dalam Valas",'EA_IBL'[1. 6 Giro Dalam Valas]),
SELECTEDVALUE(Pilihan[Slicer])="Tagihan Lainnya Dalam Valas", SELECTCOLUMNS('EA_IBL', "Tagihan Lainnya Dalam Valas",'EA_IBL'[1. 7 Tagihan Lainnya Dalam Valas])
)

3

SWITCH(
SELECTEDVALUE(Pilihan[Slicer]),
"Penempatan Pada EPM", SELECTCOLUMNS('EA_IBL',"Penempatan Pada EPM",'EA_IBL'[1. 1 Penempatan Pada EPM]),
"Surat Berharga Dalam Valas", SELECTCOLUMNS('EA_IBL', "Surat Berharga Dalam Valas",'EA_IBL'[1. 2 Surat Berharga Dalam Valas]),
"Hak Tarik Khusus", SELECTCOLUMNS('EA_IBL', "Hak Tarik Khusus", 'EA_IBL'[1. 3 Hak Tarik Khusus]),
"Deposito Dalam Valas", SELECTCOLUMNS('EA_IBL', "Deposito Dalam Valas",'EA_IBL'[1. 4 Deposito Dalam Valas]),
"Emas", SELECTCOLUMNS('EA_IBL', "Emas",'EA_IBL'[1. 5 Emas]),
"Giro Dalam Valas", SELECTCOLUMNS('EA_IBL', "Giro Dalam Valas",'EA_IBL'[1. 6 Giro Dalam Valas]),
"Tagihan Lainnya Dalam Valas", SELECTCOLUMNS('EA_IBL', "Tagihan Lainnya Dalam Valas",'EA_IBL'[1. 7 Tagihan Lainnya Dalam Valas])
)

Please the advice, thanks

[v-tangjie-msft · Accepted solution]

Hi @Dwesty_A ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) Click "transform data" to enter the power query, copy a EA_IBL table, open "Advanced Editor" and copy and paste the following code, please check the steps in the right step column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jcu5DQAwCAPAXVyngPyZBbH/GjHQWOYszKBoUGGsiCfwZuisPe4tOZcO9hFwUrV0ss+Am8o39w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, #"1. 1 Penempatan Pada EPM" = _t, #"1. 2 Surat Berharga Dalam Valas" = _t, #"1. 3 Hak Tarik Khusus" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"1. 1 Penempatan Pada EPM", Int64.Type}, {"1. 2 Surat Berharga Dalam Valas", Int64.Type}, {"1. 3 Hak Tarik Khusus", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"id"}, "Attribute", "Value"),
    #"Split Column by Positions" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 5}), {"Attribute.1", "Attribute.2"})
in
    #"Split Column by Positions"

(3) We can create a model relationship.

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[123abc]

Here are a few suggestions and improvements:

1. Simplify the Measures: You can simplify your measures by using the SWITCH statement directly on the column you want to select. For example:

ClusterMeasure =
SWITCH (
SELECTEDVALUE(Pilihan[Slicer]),
"Penempatan Pada EPM", 'EA_IBL'[1.1 Penempatan Pada EPM],
"Surat Berharga Dalam Valas", 'EA_IBL'[1.2 Surat Berharga Dalam Valas],
"Hak Tarik Khusus", 'EA_IBL'[1.3 Hak Tarik Khusus],
"Deposito Dalam Valas", 'EA_IBL'[1.4 Deposito Dalam Valas],
"Emas", 'EA_IBL'[1.5 Emas],
"Giro Dalam Valas", 'EA_IBL'[1.6 Giro Dalam Valas],
"Tagihan Lainnya Dalam Valas", 'EA_IBL'[1.7 Tagihan Lainnya Dalam Valas],
BLANK()
)

Debugging Blank Results: If your chart is showing blank when you use multiple selections, you might want to add debugging steps. For instance, you can create a separate measure to display the selected value and see if it matches what you expect. This can help you identify any issues with your slicer or data.

SelectedValueDebug = SELECTEDVALUE(Pilihan[Slicer])

1. Check Data Relationships: Ensure that there are proper relationships between the tables involved in your measures. Without the correct relationships, your measures may not work as expected, especially when using slicers.

2. Verify Data: Check if there is data available for the selected combinations. It's possible that when multiple values are selected, there might not be data for the combined selections, resulting in a blank chart.

3. Use a Table Visualization: Instead of a chart, use a table visualization to display the results of your measures. This can help you see the data that is being returned for different selections.

By simplifying your measures and adding debugging steps, you should be able to identify and address the issue causing the blank chart when using multiple selections.

[Dwesty_A]

After I debug, the multiple select get blank value, but I am using concatenatex I get the value.  For relationship I so confuse, cause in my case this data get from excel that contain several table in one sheet. And in process power query, I transform  from row to column. 

So for slicer I create datatable, for select the column. I dont have an idea to make relation from table to several column. And the column name have number for several column. Example  slicer "Penempatan Pada EPM" is define for column "1. 1 Penempatan Pada EPM" , "1. 2 Penempatan Pada EPM" and "1. 3 Penempatan Pada EPM".

[123abc]

If you have transformed your data from rows to columns in Power Query and are dealing with multiple columns with similar names like "1.1 Penempatan Pada EPM," "1.2 Penempatan Pada EPM," and so on, you may face challenges in creating relationships and slicers directly. In this case, using CONCATENATEX might be a workaround, but it's important to understand the implications of such an approach.

If you want to create a relationship between your slicer and the columns in your table, you may need to reshape your data to have a more traditional structure. However, if you prefer to keep your current structure, you can still achieve your goal using CONCATENATEX.

Here's an example of how you might modify your measure to work with multiple selections:

ClusterMeasure =
CONCATENATEX (
VALUES ( Pilihan[Slicer] ),
SWITCH (
Pilihan[Slicer],
"Penempatan Pada EPM", 'EA_IBL'[1.1 Penempatan Pada EPM],
"Surat Berharga Dalam Valas", 'EA_IBL'[1.2 Surat Berharga Dalam Valas],
"Hak Tarik Khusus", 'EA_IBL'[1.3 Hak Tarik Khusus],
"Deposito Dalam Valas", 'EA_IBL'[1.4 Deposito Dalam Valas],
"Emas", 'EA_IBL'[1.5 Emas],
"Giro Dalam Valas", 'EA_IBL'[1.6 Giro Dalam Valas],
"Tagihan Lainnya Dalam Valas", 'EA_IBL'[1.7 Tagihan Lainnya Dalam Valas]
),
", "
)

This measure concatenates the values for each selected slicer item into a single text string, separated by commas. Make sure to adjust it according to your specific needs.

Keep in mind that using CONCATENATEX in this way may limit your ability to perform certain operations later, as the result is a text string rather than a numeric or aggregated value. If possible, consider reshaping your data in Power Query to have a more traditional structure with separate columns for each attribute, making it easier to work with in DAX.