Return values from SLICER from a related table

[PaulDBrown]

Hi everyone,

I'm hoping for some help with what seems like a simple task. I'm trying to display the values from a related table as selected in a slicer a slicer in a card visual. I'm using CONCATENATEX to account for the instances when more than one item is selected in the slicer. I'm obviously doing something wrong, since the measure I'm using returns all the values; in other words, the measure doesn't work. Here is a graphical representation of the model and the output sought after:

I am expecting the output to be "AAA" if the ITEM slicer has "B" selected.

Can someone point me in the right direction??

Thank you!

Regards,

Paul.

[Zubair_Muhammad · Accepted solution]

@PaulDBrown

Give this a shot

Measure =
CALCULATE (
    FIRSTNONBLANK ( Category[Category], 1 ),
    CROSSFILTER ( 'ITEM'[Sub-Category], SubCategory[Sub-Category], BOTH ),
    CROSSFILTER ( SubCategory[Category], Category[Category], BOTH )
)

[PaulDBrown]

@Zubair_Muhammad

I worked it out! I used CONCATENATEX  in your measure (en lieu of the FIRSTNONBLANK ( Category[Category], 1 ) part and apparently it works (returns multiple values according to the selections in the slice)r.

Just for the benefit of those looking for a similar solution, this is the final measure to cater for multiple selections:

Value of Category CONCATENATX = 
CALCULATE (
    CONCATENATEX(Category; Category[Category]; ", ");
    CROSSFILTER ( 'ITEM'[Sub-Category]; SubCategory[Sub-Category]; BOTH );
    CROSSFILTER ( SubCategory[Category]; Category[Category]; BOTH )
)

Thanks again Zubair. I struggled with this for hours!

PS: I'm actually intrigued as to how this would be tackled using RELATEDTABLE, if at all possible, since I was under the impression that the functions RELATED and RELATEDTABLE allowed you to reference a table linked via a one-to-many relationship with another.

[PaulDBrown]

@Zubair_MuhammadIt works like a charm! thank you! Is there a way to cater for mutliple selections in the slicer? (that's why I was trying to use CONCATENATEX originally...)

Thank you again!

Paul.