Display subset having same property as single selection

[Baptiste42]

Hello,

I've been hitting my head on this one for a couple days now, unable to find a solution, although the problem seems quite simple.

Here's a simplified version of my data model, with dummy data at the end of the post :

- Contract table : 

• Unique key : ContractKey
• ProgramId : an attribute that multiple contracts may have in common
• Various other attributes...

- Coverage table (helps to say if a contract is Excluding, Including, Or silently including coverage for a specific risk type) :

• Unique key : ContractKey, RiskType
• Coverage column : either "Excluded", "Included", "Silent"

The two table are linked 1 to 1 on ContractKey.

In the dashboard I have multiple slicers and visual that leads to a standard table visual (A), in which is displayed the list of selected contracts with some attributes.

When a row is selected in A, I want to display in another visual B (matrix) :

as rows : all the contracts having the same ProgramId as the selected one

as columns : the risk types from the coverage table

as values : the coverage columns of the coverage table (i.e. Excluded, Included, Silent)

- Displaying a matrix with the coverage value isn't an issue, I've managed to do it easily with something like this :

FirstCoverage CALCULATE(

    MAX(Coverage[Coverage]),

    ALL('Contract informations'),

    FILTER(

        ALL('Contract informations'),

        'Contract informations'[ProgramId] = "123456" /* Dummy value, not dynamic*/

    ))

- Finding the value of the selected programId only when a selection is made isn't an issue either, i have the following measure :

SelectedProgramId = IF(ISFILTERED('Contract informations'[ContractKey]),

                    SELECTEDVALUE('Contract informations'[ProgramId]),

                    BLANK())

However, I really can't find a way to display more contracts in B than the one selected in A, dynamically based on the selection. The FirstCoverage measure will work if a ProgramId value is hard coded, I do get all the contracts from that program in the matrix. But as soon as I put the SelectedProgramId measure instead, it only shows the selected contract in the matrix.

 

ContractKey	ProgramId	Text
1	X	a
2	Y	b
3	Y	c
4	X	d
5	X	e

ContractKey	RiskType	Coverage
1	Type1	Included
1	Type2	Included
2	Type1	Excluded
2	Type2	Included
3	Type1	Silent
3	Type2	Included
4	Type1	Included
4	Type2	Silent
5	Type1	Excluded
5	Type2	Excluded

E.g. in relation with the below dummy data : If a row with contract 3 is selected in A, i want to display in B

	Type1	Type2
2	Excluded	Included
3	Silent	Included

Thanks in advance,

Baptiste

[Baptiste42]

@bhanu_gautam , I had tried this thing with the CREATETABLE expression but couldn't work it out, the expression that you gave me returns the error "A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression"

[bhanu_gautam]

@Baptiste42 , Try using below method

Create a measure to capture the selected ProgramId:
SelectedProgramId =
IF(
ISFILTERED('Contract informations'[ContractKey]),
SELECTEDVALUE('Contract informations'[ProgramId]),
BLANK()
)

Create a measure to filter contracts based on the selected ProgramId:
FilteredContracts =
CALCULATETABLE(
'Contract informations',
'Contract informations'[ProgramId] = [SelectedProgramId]
)

Create a measure to display the coverage values dynamically:
DynamicCoverage =
CALCULATE(
MAX(Coverage[Coverage]),
FILTER(
ALL('Contract informations'),
'Contract informations'[ProgramId] = [SelectedProgramId]
)
)

Set up your matrix visual (B):
Rows: Use ContractKey from the Contract informations table.
Columns: Use RiskType from the Coverage table.
Values: Use the DynamicCoverage measure.
By setting up the matrix visual this way, it will dynamically display the coverage values for all contracts that share the same ProgramId as the selected contract in visual A.