show items with no data on a visual from two tables with an additional filter (example data avail.)

[CoTheiss]

Hi there !

hope you can help me solving this issue.

I have two tables - Contracts and Contract details.

Unfortunately, not every contract has a line in contract details, but it may have also multiple lines -  so the relationship is 1 - 0..n.

It is set bidirectional in PowerBI.

As I needed it for another visual, there is already a field existing in Contracts that shows True/False whether there are Details for this contract or not (solved in SQL beforehand, not PowerBI).

The visual I need is a list of all contracts that I have, plus the latest details (if available).

The hard part of it is the combination of "latest details" and "if available". 

I can solve each of it separately, but once I combine both approaches, the table stays blank when there are no Contract Details, as the filter in the field "IsMaxPeriod" from the ContractDetails is not just null, but even does not exist for e.g. ContractID 456.

In my mind, this table should be super easy, as it is just a

SELECT * FROM Contracts LEFT JOIN (SELECT * FROM ContractDetails WHERE IsMaxPeriod =1) [...]

but on a visual level - as I do need the other Periods for other visuals still.

Unfortunately, I'm better with SQL and other BI tools than with Power BI - so I do hope someone here has some PowerBI-Tipps on how to solve it for this specific visual 🙂

[v-xingshen-msft]

Hi All,

Firstly,  @Anonymous  thank you for your solution!

And @CoTheiss  according to my understanding,  I want to share another method.

If you want to see all the information, we can re-new the table in Power BI to do what you need.

This calculated column serves to find the latest end date for each contract.

LatestPeriodTo = 
VAR LatestPeriod =CALCULATE(
    MAX('ContractDetails'[PeriodTo]),
    FILTER('ContractDetails','ContractDetails'[ContractID]='Contracts'[ContractID])
)
RETURN
IF('Contracts'[HasContractDetails],
LatestPeriod,
BLANK()
)

Use CALCULATETABLE and FILTER to find the latest details for each contract.
Use MAXX to extract the latest PeriodFrom and PeriodTo dates.
Set IsMaxPeriod to TRUE if the contract has details, otherwise null (BLANK()).

LatestContractDetails = 
SELECTCOLUMNS(
   ADDCOLUMNS(
       Contracts,
       "PeriodFrom",
       VAR LatestDetails =
           CALCULATETABLE(
               FILTER(
                   ContractDetails,
                   ContractDetails[IsMaxPeriod] = TRUE &&
                   ContractDetails[ContractID] = RELATED(Contracts[ContractID])
               )
           )
       RETURN
       MAXX(LatestDetails, ContractDetails[PeriodFrom]),
       "PeriodTo",
       VAR LatestDetailsTo =
           CALCULATETABLE(
               FILTER(
                   ContractDetails,
                   ContractDetails[IsMaxPeriod] = TRUE &&
                   ContractDetails[ContractID] = RELATED(Contracts[ContractID])
               )
           )
       RETURN
       MAXX(LatestDetailsTo, ContractDetails[PeriodTo]),
       "IsMaxPeriod",
       IF(
           Contracts[HasContractDetails],
           TRUE,
           BLANK()
       )
   ),
   "ContractID", Contracts[ContractID],
   "ContractName", Contracts[ContractName],
   "Responsible", Contracts[Responsible],
   "PeriodFrom", [PeriodFrom],
   "PeriodTo", [PeriodTo],
   "IsMaxPeriod", [IsMaxPeriod]
)

If you have any other questions, check out the attachments I've added that may help you.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[Anonymous]

Hi @CoTheiss 
make a relation from Contract to contract detail from 1 to * single direction and create 3 measures as i had created in below screenshot.

I am getting the desire output.