Context/Totals Issue with ConcatenateX in virtual table

DAX DAX Calculations
1

Hi Guys

I seem to have come across an issue trying to do something so simple, but so frustrating.

I have a hierarchical slicer Company | Division | Business Unit. This slicer will be on a menu page with other report pages sync’d to the slicer but without the slicer visible. So I need a card on the report pages with a measure identifying which items are selected from the menu page.

In it’s simple form this is a straight forward CONCATENATEX. But this could would truncate the text on the card, especially if all items are selected.

If all sites are selected this is straight forward - if count selected rows = count all rows then return “Group” or “All”.

My problem arise with the following:
Given 2 divisions with a number of sites, if all sites in the division are selected, I’d like to return the division name otherwise the concatenation of the selected Business units.

I have taken the approach of creating a virtual table to determine Division counts (all and selected), and can derive the division name and/or the list of business units but when trying to concatenateX the virtual table it only returns the concatenation of the final division and ignores the first division.

In the example PBIX attached the measure Picked Sites should return East Division, Liverpool, Leeds.

I think the issue lies amongst the theory on totals and subtotals in matrixes, but the help here tends to refer to physical tables and numbers rather than virtual tables and text.

comselection.pbix (26.9 KB)

Any inspiration here would be greatly appreciated.

Once tested on the 2 tier hierarchy I will need to expand the logic for multiple companies as well.
This should be easy enough with a Switch statement once the core problem is sorted.

Thanks
Pete

PS:
@BrianJ - good news Brian, going part time tomorrow!!

1 Like
2

Bumping this post for more visibility.

3

Hi @BINavPete

Try this Dax Measure

Selected Sites = 
VAR CR_LF = "
"

VAR _Company =
    IF (
        ISFILTERED ( Companies[Company] ),
        CONCATENATEX (
            ALLSELECTED (Companies[Company]),
            Companies[Company]
            ,
            ", "
        ),
        "All"
    ) 
 VAR _Division =
    IF (
        ISFILTERED ( Companies[Division] ),
        CONCATENATEX (
            ALLSELECTED (Companies[Division]),
            Companies[Division]
            ,
            ", "
        ),
        "All"
    ) 
 VAR _BU =
    IF (
        ISFILTERED ( Companies[BusinessUnit] ),
        CONCATENATEX (
            ALLSELECTED (Companies[BusinessUnit]),
            Companies[BusinessUnit]
            ,
            ", "
        ),
        "All"
    )    
Return 
    IF(_Company <> "", "Company: " & _Company & CR_LF) &
    IF(_Division <> "", "Division(s): " & _Division & CR_LF) &
    IF(_BU <> "", "BU: " & _BU & CR_LF)

image
image1586×919 48.9 KB

1 Like
4

Thank you so much for that detailed answer @jbressan

We hope this helped you @BINavPete :slight_smile:

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

5

Hi guys
Thanks @jbressan. This wasn’t quite what I had in mind, but really helpful.

I liked the CR_LF variable. Gonna use that a lot.
I think I can now make a single button/text box using this measure to fit my solution with 3 lines. So rather than 3 separate ones side by side, a wider one with more height should cater for my needs.

From a tech viewpoint though, would be good to have the measure return “All North Division” when all north division business units are selected, then button/textbox not cluttered with list of BUs.

SO if anyone fancies that challenge, be grateful.

But thanks again @jbressan. BIG HELP!!

Pete