Display data in a table visual using two linked tables

[ksivaganesh99]

Hi all,

I have two tables linked by a linkedID. Below are the two tables.

Table1:

ID	Status	Type	State	Suggestion	Owner	LinkedID
123	New	High	High	Avoid	Thomas Edison	1
124	New	High	High	Avoid	Monique Cook	2
124	New	High	High	Avoid	Monique Cook	3
124	New	High	High	Avoid	Monique Cook	4

Tale 2:

LinkedID	Title	Status
1	WorkItem1	Done
2	WorkItem2	Done
3	WorkItem3	Done
4	WorkItem4	Done

I want to have them displayed in a tabular format as shown below. How can I acheive it?

Any help is highly appreciated. Thanks.

[Icey]

Hi @ksivaganesh99 ,

Is this what you want?

If it is, create a Matrix visual like so:

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ksivaganesh99]

Hi @Icey ,

Thank you so much for the reply. I want background colors of State column to be changed based on their values. Is it possible with your solution?

Thank you again for the help.

[Icey]

Hi @ksivaganesh99 ,

In Matrix visual, if the columns are in "Rows" or "Columns" fields, Power BI is not support to apply conditional formatting on them. It is suggested to put the columns you want to apply conditional formatting to "Values" field.

Measure =
SWITCH (
    MAX ( Table1[State] ),
    "High", "red",
    "Medium", "orange",
    "Low", "blue"
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ksivaganesh99]

Hi @Icey 

Thanks for the reply. I appreciate your approach but I don't want the "State" to be repeated for all the linked items when I put it in "Values" section. Is there a way we can acheive it?

I want something like this (edited in paint😀)

Any help is highly appreciated.

[Icey · Accepted solution]

Hi @ksivaganesh99 ,

How about this:

1. Create a [State Measure] like so:

State Measure =
VAR Previous_ =
    CALCULATE (
        MAX ( Table1[State] ),
        FILTER ( ALLSELECTED ( Table2 ), Table2[Title] < MAX ( Table2[Title] ) )
    )
RETURN
    IF ( Previous_ = MAX ( Table1[State] ), BLANK (), MAX ( Table1[State] ) )

2. Change [Measure] like so:

Measure = 
SWITCH (
    [State Measure],
    "High", "red",
    "Medium", "orange",
    "Low", "blue"
)

3. Replace [State] column with [State Measure] in the Matrix visual.

4. Set conditional formatting.

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

I'm not sure I understand what you want. Sounds like you need a matrix visualization but after that, I can't tell if I am looking at source data, current output, expected output or ? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[ksivaganesh99]

Thank you for the reply. I updated the question with required details. Any help is very much appreciated.