Data Relationship Question

[Anonymous]

Hi,

I have a project database, very simplistically set up as following. A 'projects' table listing all the projects, and a risk table listing risks, joined to the project table. (In reality there are many tables but this should suffice as an example)

I Have a programme report where a user chooses the programme they wish to view, and then the report is filtered to show projects and risks associated with that programme.

All simple and working so far. 

However...one project is now reporting under two programmes, lets call this "Project HW&SW".

How can the data or risk report be set up so that when a user selects either Hardware OR Software programme, the data associated with that project is returned.

Currently there is only one project like this, but eventually there may be more.

So the report would look like the following, with the Project HW&SW showing when filtering on Hardware.

[Anonymous]

Hi Allan, would you be able to explain how this works?

[v-alq-msft]

Hi, @Anonymous 

The measure checks if 'Programme' for current context is in the selected items of the slicer and if there is an item in selected items of the slicer satisfy that current item has 'Hw' or 'Sw' in 'Project' for current context. Hope it helps.

Best Regards

Allan

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Projects:

Risks:

Slicer(a calculated table):

Slicer = DISTINCT(Projects[Programme])

There is a relationship between 'Projects' and 'Risks'. You may create a measure as below.

Visual Control = 
var c = 
COUNTROWS(
    FILTER(
        DISTINCT(Slicer[Programme]),
        CONTAINSSTRING(SUBSTITUTE(MAX(Projects[Project]),"Project",""),LEFT([Programme],1)&RIGHT(LEFT([Programme],5),1))
    )
)+0
return
IF(
    MAX(Projects[Programme]) in DISTINCT(Slicer[Programme]) || c>0,
    1,0
)
    

Then you need to put the measure in the visual level filter to filter the result.

Best Regards

Allan

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

[amitchandak]

@Anonymous , You can take out table from project table

summarize(Project, Project[projects],Project[programme]) and join with both tables. and filter

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[amitchandak]

@Anonymous , where is that column in your data in project table ?

[Anonymous]

Hi Amit, which column are you asking about?

The main column of interest is the 'Programme' column as that is what is used to filter the programme report.