Forum Discussion
Display subset having same property as single selection
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 :
| 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
2 Replies
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.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"
