Help with my Power BI model

[kevcb226]

Hi,

I'm hoping someone can help me to better understand how I can setup my Power BI model.

I have a model with two tables one with Targets and one with Applications. The two models contain a site identifier. The Target table which contains a distinct list of sites with a number of targets associated to them, and the Applications table which contains a list of applications for each site (essentially a one to many relationship).

Some of the sites do not have any applications yet, but on my main BI page, I want to show the grand total of targets. Now if I create a relationship between the two tables using the site identifier, I only get the total of targets for those sites with applications linked. However, I don't want this to happen, as I want to also include the targets for sites that do not have an applications (similar to a SQL left join to count all the targets).

I've tried with not having a relationship defined, which gives me the total of the targets, which is correct. But because I have no relationship defined, this then stops any of the slicers on the page filtering the rest of the data which contains counts of applications.

In the image above the numbers next to the target label at the top of the page are all from the Target table, the numbers in the bottom part of the page and the graphs are all from the Applications table.

I think I may need to create some DAX measures that can filter the data based on the slicers without having a relationship, so that the application counts change. Is this possible?

Many Thanks

[v-yangliu-msft · Accepted solution]

Hi  @kevcb226 ,

I created some data:

Table:

Slicer Table:

We can use a slicer to filter when there is no relationship, but only if there is a value in this table that directly matches or can match the value selected by this slicer after filtering by Dax, so that

Custom rules:

By slicer selection, only data matching the slicer in Visual is displayed

Then use Measure to calculate the total value after filtering

Here are the steps you can follow：

1. Create measure.

Flag =
var _select=SELECTCOLUMNS('Table2',"year",[Year])
return
IF(
    YEAR(MAX('Table'[Date])) in _select,1,0)

Measure =
SUMX(
    ALLSELECTED('Table'),[Value])

 2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

If I have misunderstand your meaning, please contact me and provide a pbix without privacy data.

Best Regards,

Liu Yang

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