Forum Discussion

Dilbertfan's avatar
Dilbertfan
Frequent Visitor
1 year ago
Solved

Lookup & Concatenate Values in a Matrix

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 ...
Need Help
  • ABD128's avatar
    ABD128
    1 year ago

    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) )

freginier's avatar
freginier
Icon for Solution Sage rankSolution Sage
1 year ago
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's avatar
Dilbertfan
Frequent Visitor
1 year ago

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.