Lookup & Concatenate Values in a Matrix

[Dilbertfan]

Hi All

Some time ago I posted a request for help (LINK) to compare two datasets names and their sensitivity, lookup up the combination value and then returning it at the intersection of a matrix view.  @barritown was really helpful in giving me the answer which worked fine.

On the same report, I now have a related but different request which I have played around with for ages.  Basically we have now extended the dataset name, so as well as a sensitivity value to compare, each dataset can have beetween 0 and five use cases with it.  Each combination of use cases can be compared against each other for each datasets selected (the sensitivity doesn't matter - this is a different section of the report)

I havent included a pbix or data file with this as not sure how the data should be structured for this.

The Data

In the original ask, each dataset had ONE sensitivity value

We also now have the datasets with their use cases which need to be compared

From this I have created a list by unpivoting which I think is needed to compare the two values.  

And then, I have created a disconnected version of this table so I have two identical tables which are can be filted by a slicer which is synced to a second disconnected slicer so that the datasets selected stay in sync (just like in the original above)

Finally I have a look up table to compare the two values (This is part of it)

The Ask

What I would like to do is create a similar matrix to the sensitivity rating, but this time rather than the dataset being the row / column, I would like the use case to be the row column and then where they interset, to insert :

• the dataset combinations which match the intersection
• for each combination, look up the outcome and show this against the combination

So you end up with something like this:

I haven't put all the combinations in or put the right lookups in, but hopefully this gives you an idea.

I have been looking at a CONCATENATEX measure along these lines which brings the tables together and then concatenates the datasets from dataset 1 and dataset 2 tables where they intersect the user cases.

Look Up & Match Use Case (Concatenate Dataset) = 

VAR ds1 = SELECTEDVALUE('fctSiPAssets 1'[Dataset ID])
VAR ds2 = SELECTEDVALUE('fctSiPAssets 2'[Dataset ID])

VAR s1 = SELECTEDVALUE('fctSiPAssets 1'[UseCaseNo])
VAR s2 = SELECTEDVALUE('fctSiPAssets 2'[UseCaseNo])

VAR _temptable = 

    FILTER(
        SUMMARIZECOLUMNS(
        'fctSiPAssets 1'[Dataset ID],
        'fctSiPAssets 1'[UseCaseNo],
        'fctSiPAssets 2'[Dataset ID],
        'fctSiPAssets 2'[UseCaseNo]
        ),
        'fctSiPAssets 1'[Dataset ID] = ds1 && 
        'fctSiPAssets 2'[Dataset ID] = ds2
    )

RETURN 

CONCATENATEX(
    _temptable,
    'fctSiPAssets 1'[Dataset ID] & 
    " | " & 
    'fctSiPAssets 2'[Dataset ID] & 
    " - " &                         -- Need to put the outcome in here
    UNICHAR(10)
)

However, this doesn't look right and certainly doesnt return the right result (although it does return a number of results, which leads me to think I am on the righ(ish) track and also doesn't solve how i look up the outcome for each dataset where they interest in the matrix.

Hope that this makes sense; I thought I had my head around it after Barritone's solution for my previous and related requirement, but I just cant get it to work

Thanks for this in advance and if you can let me know how to structure some data, happy to put tother a pbix file.

Rich

[ABD128 · Accepted solution]

Hi @Dilbertfan 

Try the below DAX:

Look Up & Match Use Case (Concatenate Dataset) =

VAR UseCaseRow = SELECTEDVALUE('fctSiPAssets 1'[UseCaseNo])

VAR UseCaseCol = SELECTEDVALUE('fctSiPAssets 2'[UseCaseNo])

-- Get all datasets selected in slicers

VAR SelectedDataset1 = VALUES('fctSiPAssets 1'[Dataset ID])

VAR SelectedDataset2 = VALUES('fctSiPAssets 2'[Dataset ID])

-- Build dataset combinations via CROSSJOIN

VAR AllDatasetCombinations =

ADDCOLUMNS(

CROSSJOIN(

SelectedDataset1,

SelectedDataset2

),

"Dataset1_Alias", [Dataset ID],

"Dataset2_Alias", [Dataset ID] )

-- Filter combinations by UseCase intersection

VAR FilteredCombinations =

ADDCOLUMNS(

AllDatasetCombinations,

"CombinationText",

VAR CurrentDataset1 = [Dataset1_Alias]

VAR CurrentDataset2 = [Dataset2_Alias]

-- Lookup Outcome

VAR OutcomeFound =

LOOKUPVALUE(

'DatasetOutcome'[Outcome],

'DatasetOutcome'[Dataset 1], CurrentDataset1,

'DatasetOutcome'[Dataset 2], CurrentDataset2 )

-- Check if datasets match row and column UseCase

VAR MatchRow =

CALCULATE(

COUNTROWS('fctSiPAssets 1'),

'fctSiPAssets 1'[Dataset ID] = CurrentDataset1,

'fctSiPAssets 1'[UseCaseNo] = UseCaseRow

) > 0

VAR MatchCol =

CALCULATE(

COUNTROWS('fctSiPAssets 2'),

'fctSiPAssets 2'[Dataset ID] = CurrentDataset2,

'fctSiPAssets 2'[UseCaseNo] = UseCaseCol

) > 0

RETURN

IF(MatchRow && MatchCol && NOT ISBLANK(OutcomeFound),

CurrentDataset1 & " & " & CurrentDataset2 & " = " & OutcomeFound,

BLANK() ) )

RETURN

CONCATENATEX( FILTER(FilteredCombinations, NOT ISBLANK([CombinationText])), [CombinationText], UNICHAR(10) )

[Dilbertfan]

Hey @ABD128 - This is fantastic, thank you so much - it worked how I wanted it to.  I realised that I was trying to do this on real data (impossible to check all results) rather than do a sample Power BI Report which much simpler data, so I created something very small to also validate the results

The only change I had to make to your code was in the followign where it wouldnt accept the Dataset ID for the new columns, but changing it to the original column name worked work.

I left out the otucome for each intersection as realised it was pointless as they would be the same at each intersection of the matrix, so I can do a bit of conditional formatting on this.

Thank you again for this.  Whilst in my head I understood the steps to build the query, and I understand the logic of how you have written it, I couldn't have come up with this from scratch in terms of all the components. 

This forum constantly amazes me with how you can come up with these things based on an obscure requirement - even more impressive without any source data - and not strictly data analysis.  Also how versatile a tool Power BI is.

Rich

[v-karpurapud]

Hi @Dilbertfan 

We were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.  Could you please confirm if your query has been resolved the solution provided by @freginier ? If it has, kindly mark the helpful response and accept it as the solution. This will help other community members resolve similar issues more efficiently. If not, please provide detailed information so we can assist you better.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

[freginier]

Try it this way and keep me updated if it works :

Look Up & Match Use Case (Concatenate Dataset) =
VAR UseCaseRow = SELECTEDVALUE('fctSiPAssets 1'[UseCaseNo])
VAR UseCaseCol = SELECTEDVALUE('fctSiPAssets 2'[UseCaseNo])

VAR SelectedDataset1FromSlicer = SELECTEDVALUE('fctSiPAssets 1'[Dataset ID])
VAR SelectedDataset2FromSlicer = SELECTEDVALUE('fctSiPAssets 2'[Dataset ID])

IF(ISBLANK(UseCaseRow) || ISBLANK(UseCaseCol), BLANK(),
    VAR BaseDatasetsInRowUseCase =
        CALCULATETABLE(
            VALUES('fctSiPAssets 1'[Dataset ID])
        )

    VAR DatasetsForCurrentRow =
        IF(
            NOT ISBLANK(SelectedDataset1FromSlicer),
            FILTER(BaseDatasetsInRowUseCase, 'fctSiPAssets 1'[Dataset ID] = SelectedDataset1FromSlicer),
            BaseDatasetsInRowUseCase
        )

    VAR BaseDatasetsInColUseCase =
        CALCULATETABLE(
            VALUES('fctSiPAssets 2'[Dataset ID])
        )

    VAR DatasetsForCurrentCol =
        IF(
            NOT ISBLANK(SelectedDataset2FromSlicer),
            FILTER(BaseDatasetsInColUseCase, 'fctSiPAssets 2'[Dataset ID] = SelectedDataset2FromSlicer),
            BaseDatasetsInColUseCase
        )

    VAR AllDatasetCombinations =
        ADDCOLUMNS(
            CROSSJOIN(
                SELECTCOLUMNS(DatasetsForCurrentRow, "Dataset1_Alias", 'fctSiPAssets 1'[Dataset ID]),
                SELECTCOLUMNS(DatasetsForCurrentCol, "Dataset2_Alias", 'fctSiPAssets 2'[Dataset ID])
            ),
            "CombinationText",
            VAR CurrentDataset1 = [Dataset1_Alias]
            VAR CurrentDataset2 = [Dataset2_Alias]

            VAR OutcomeFound =
                LOOKUPVALUE(
                    'DatasetOutcome'[Outcome],
                    'DatasetOutcome'[Dataset 1], CurrentDataset1,
                    'DatasetOutcome'[Dataset 2], CurrentDataset2,
                    BLANK()
                )
            RETURN
                IF(
                    NOT ISBLANK(OutcomeFound),
                    CurrentDataset1 & " & " & CurrentDataset2 & " = " & OutcomeFound,
                    BLANK()
                )
        )

    RETURN
        CONCATENATEX(
            FILTER(AllDatasetCombinations, NOT ISBLANK([CombinationText])),
            [CombinationText],
            UNICHAR(10),
            [CombinationText], ASC
        )
)

[Dilbertfan]

Hi @freginier 

Firstly thanks for taking the time to respond and for a really comprehensive measure.  Glad to see i wasn't totally on the wrong track.

I am getting an error in the measure when it comes to the Crossjoin and using Selectolumns - see below. 

It obviously wants a table, but it thinks it is getting a string or numeric expression.  I can't quite work this out as BaseDatasetsInColUseCase is a table and this continues in DatasetsForCurrentCol with either it being referred direct or with a filter which should result in a table again.

If I remove the IF statement from DatasetsFromCurrentColumn then it works fine

I have spent a bit of time playing with it, but still can't get it to accept the two variables - any ideas?

However, If I do remove the IF statement but make sure that have the slicers set, the measure returns nothing in the matrix, so don't think the measure is working, which is strange as it all seems logical.

Is it anything to do with that we can select multiple datasets and then compare all the use cases in each.  Would the selected value take this into account

Rich

p.s. ignore me changing Dataset ID to Asset ID, it is just how we reference them now - in the above, I was trying to keep to the original question and the terminology used.