Power Bi Measure with non aggregated String

Question

Is it possible to let a Measure show multiple String values, which are not aggregated using CONCATENATEX(), FIRSTNONBLANK() etc.?

My Data looks like this:

id	MaterialNumber
a	1
a	2
a	3
b	1
b	3
c	2
c	3

Goal I want to achieve in Power BI:

Slicing to id = a
displaying Materialnumbers of id = a
choose a materialnumber, e.g. Materialnumber = 1
Displaying all id's in a matrix where Materialnumber = 1, in this case a & b. The rest of the visuals should stay filtered.

I failed to write a measure for step 4).
This one has the right functionality, but I want to display separate Strings (to copy them out of the matrix):

Selected Associated Ids = 

CALCULATE(
  CONCATENATEX(
   Data
   ,Data[id]
   ,UNICHAR(10)
  )
  ,FILTER(
    all(Data)
    ,Data[MaterialNumber] = SELECTEDVALUE(Report[MaterialNumber])
  )
)

I also tried this, which failed due to the well known Error:

A table of multiple values was supplied where a single value was expected.

A lot of people solve this by using FIRSTNONBLANK() which does not fit my case, because i want to have all results, not only the first one. In this case, a & b.:

LOOKUPVALUE(
  Report[PATH_ID]
  ,Report[MATNR]
  ,SELECTEDVALUE(Report[MATNR])
)

Using Power BI Desktop Cloud 2.86.902.0

Alexis Olson Alexis Olson · Accepted Answer · 2021-01-08T18:09:40

You need a separate table to act as a slicer on id and MaterialNumber.

A calculated table like:

Slicer = SUMMARIZE ( Data, Data[id], Data[MaterialNumber] )

Then you can filter your matrix visual with a measure in the filter pane, i.e., [ShowId] = 1.

ShowId =
IF (
    SELECTEDVALUE ( Slicer[MaterialNumber] )
        IN CALCULATETABLE (
            VALUES ( Data[MaterialNumber] ),
            ALLEXCEPT ( Data, Data[id] )
        ),
    1
)

Power Bi Measure with non aggregated String

1 Answers