Hello,
I am creating a matrix that shows a list of vessels, and has detail rows showing the various ports that each vessel will be visiting. Each port has an associated region (Americas, Asia, Middle East, Europe/ROW). I have a "Region" slicer that does not have a relationship with the vessel data, but is being used by a measure, "Measure 1", to determine if a given Vessel/Port combination belongs to one of the selected regions. The measure assigns a value of 1 if the port belongs to a selected region, and a value of 0 if it does not.
This is all working fine, but the next step is where my issue lies. If ANY of the ports associated with a vessel belong to one of the selected regions, I want ALL of the ports for that vessel to be displayed in the matrix. My thought was to create a second measure, "Measure 2", that will sum "Measure 1" for each vessel, such that "Measure 2" could be displayed in the matrix alongside each port and still display the "Measure 1" total for the entire vessel. I would then use "Measure 2" as a visual filter in the matrix so that records where "Measure 2" > 0 would be displayed.
Here is an Excel sample showing my desired result:
| Selected Ports = Americas, Asia | | | |
| | | | | | |
| Vessel | Port | Measure 1 | Port Region | Measure 2 - Current | Measure 2 - Desired |
| Vessel 1 | Texas | 1 | Americas | 1 | 5 |
| Vessel 1 | Ecuador | 1 | Americas | 1 | 5 |
| Vessel 1 | Peru | 1 | Americas | 1 | 5 |
| Vessel 1 | Peru | 1 | Americas | 1 | 5 |
| Vessel 1 | Chile | 1 | Americas | 1 | 5 |
| Vessel 2 | South Korea | 1 | Asia | 1 | 7 |
| Vessel 2 | South Korea | 1 | Asia | 1 | 7 |
| Vessel 2 | South Korea | 1 | Asia | 1 | 7 |
| Vessel 2 | China | 1 | Asia | 1 | 7 |
| Vessel 2 | Singapore | 1 | Asia | 1 | 7 |
| Vessel 2 | Angola | 0 | Europe/ROW | 0 | 7 |
| Vessel 2 | Nigeria | 0 | Europe/ROW | 0 | 7 |
| Vessel 3 | Greece | 0 | Europe/ROW | 0 | 4 |
| Vessel 3 | Italy | 0 | Europe/ROW | 0 | 4 |
| Vessel 3 | Singaport | 1 | Asia | 1 | 4 |
| Vessel 3 | China | 1 | Asia | 1 | 4 |
In the sample above, "Measure 1" correctly identifies when a port belongs to one of the selected regions (Americas, Asia). However, "Measure 2" is not currently working as desired. It seems to simply be duplicating the logic of "Measure 1", however I would like it to represent the sum of "Measure 1" for the vessel as a whole (see "Measure 2 - Desired" column). Here is my DAX for both measures:
Measure 1 =
var SelectedRegions = CONCATENATEX(ALLSELECTED(Regions[Region]), Regions[Region], ",")
var str_SelectedRegions = SUBSTITUTE(SelectedRegions, ",", "|")
var str_len = PATHLENGTH(str_SelectedRegions)
var tbl_list = ADDCOLUMNS(
GENERATESERIES(1, str_len),
"table",
PATHITEM(str_SelectedRegions, [Value])
)
var lst = SELECTCOLUMNS(tbl_list, "list", [table])
return
SUMX(
'Voyage Schedule',
if(
'Voyage Schedule'[col_Region_port] in lst,
1,
0
)
)
Measure 2 =
sumx(
values('Voyage Schedule'[VoyageNumber]),
[Measure 1]
)
