Forum Discussion
Lookup & Concatenate Values in a Matrix
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) )
Resolver IIHi 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) )
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
