Linking two tables in visual

[elmarhogenboom]

Hi everyone,

I'm fairly new to Power BI, but somewhat advanced with Excel, which seems to hinder me in my understanding of Power BI.

I have two tables, with a one to many relationship. Table 1 consists of unit IDs, which represent unique apartments my organization rents to people with low income. Table 2 consists of contract IDs, which represent unique contracts linked to the unit IDs. 

The contracts in table 2 have a column 'End date', which is either empty (for a current contract) or filled (for an expired contract). I made a visual with the number of ended contracts per year for all apartments in a particular building, see first picture.

Building 10170101 has been renovated in 2017, so I want to filter this visual to only show 2018 and up, because the ended contracts in 2017 were in a different building, so to speak. I thought I'd do this with the help of a calculated column in table 1, with the formula [Start year] = IF([Year renovation]>2015;[Year renovation]+1;2016). If it's not been renovated, the start year is 2016, like for 10100201 and 10100401.

This gives me a new visual, see the second picture ('Eerste jaar mutatiegraad 2020' is = 'Start year'). This is based on the minimum of the calculated column [Start year], because it's the same for all units in the building.

What I'd like to see in the first picture is for 10170101 the number of ended contract to be 36, consisting of only the values for 2018, 2019 and 2020, as 'Start year' for 10170101 is 2018. I think I want a filter doing something in regular words like 'display all ended contracts for the years that are equal to or higher than [Start year]'.

I thought the best way to do this is to have a calculated column in table 2 (contracts), which gives me a yes or no for each contract with a filled end date. When [End date] is before [Start year], I want to get a no, if it's after, a yes. So I put in the formula To count = IF(YEAR(table2[End date])>=AVERAGE(Table1[Start year],"Yes","No"). Seemed pretty straightforward, but I get the wrong data. The AVERAGE part gives me an average of all start years, instead of the average start year of this particular building. I've been looking at measures and filters, but the examples of measures I see on Google and forums are either to simple (like Projected sales = sum(Sales[Total sales amount])*1,08) of way too difficult.

I'm thinking there should be a simple solution, which goes to show my newbee-ness to Power BI. Any help is greatly appreciated!

Cheers, Elmar

[v-yingjl · Accepted solution]

Hi @elmarhogenboom ,

If I got it correctly, you can create this measure and put it in the matrix:

Measure =
VAR tab =
    SUMMARIZE (
        'Contracts',
        'Contracts'[Contractnummer],
        'Contracts'[Datum uitbeheer].[Jaar],
        "Count",
        VAR _year = [Datum uitbeheer].[Jaar]
        RETURN
            CALCULATE (
                DISTINCTCOUNT ( Contracts[Contractnummer] ),
                'Units'[First year to count] <= _year
            )
    )
RETURN
    SUMX ( tab, [Count] )

Attached the modified file: VGM-data -forum.pbix

Best Regards,
Yingjie Li

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

[amitchandak]

@elmarhogenboom , Can you share sample data and sample output in table format?

[elmarhogenboom]

Thanks for your quick reply. Can I share a dropbox link with you? I'm not sure how to share only the data, as I'm afraid I would only share half of it. https://www.dropbox.com/s/17s9mqb4wzeqzwu/VGM-data%20-forum.pbix?dl=0

I've redefined the column start year, to also include the year of construction. If the building has been built in 2017 or later, I want to start counting from that year onwards, with a maximum of five years to be counted.

[v-yingjl · Accepted solution]

Hi @elmarhogenboom ,

If I got it correctly, you can create this measure and put it in the matrix:

Measure =
VAR tab =
    SUMMARIZE (
        'Contracts',
        'Contracts'[Contractnummer],
        'Contracts'[Datum uitbeheer].[Jaar],
        "Count",
        VAR _year = [Datum uitbeheer].[Jaar]
        RETURN
            CALCULATE (
                DISTINCTCOUNT ( Contracts[Contractnummer] ),
                'Units'[First year to count] <= _year
            )
    )
RETURN
    SUMX ( tab, [Count] )

Attached the modified file: VGM-data -forum.pbix

Best Regards,
Yingjie Li

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

[elmarhogenboom]

Thanks a lot, @v-yingjl, that's exactly it. I spent the better part of two hours to break the measure down on a notepad so I actually understand (kind of) what it does. Yesterday, I made a calculated column with a yes or no to be counted, which gave me the same result in the end. That was already a win. I guess the whole idea of measures is so you don't have to add lots of columns all the time.

Can I ask you a follow-up question? Now that I've got the number of contracts to be counted, I want to calculate how many contract changes have been in the years to be counted. So, I want to

DIVIDE(

       the number of contract changes;

       the number of units in the building*number of years to be counted)

So let's say for building 10170101, with 36 contract changes and 96 units in the building, and a renovation done in 2017, the result should be 36 / ( 96 * ( 2020 - 2017 )) = 36 / 288 = 12,5%. This means on average, 12,5% of the units will see a contract change.

For this, I made four calculated columns, but this should be done with measures as well, I guess. 

1) Number of units in the building (took this from a forum)

Number of units = CALCULATE( 
COUNT('Units'[Building ID]); 
FILTER( ALL('Units');'Units'[Building ID]= 
EARLIER('Units'[Building ID])))

2) Number of years to be counted (maximum 5, starting in 2016, till 2020).  Can this be done better? I tried making a VAR for AVERAGE('Year'[Year]) to make it cleaner, but that didn't work. For 10170101, this will be 3, as the renovation year = 2017, so counting 2018, 2019 and 2020.

Number of years to be counted = 
IF([Renovation year]>2015;AVERAGE('Year'[Year])-[Renovation year]; 
IF([Construction year]>2015;AVERAGE('Year'[Year])-[Construction year];5))

For this, I added a separate table with the year as a variable. So one column with one row, containing '2020'. We're calculating for 2020, but it will be that early 2021, we're still working on 2020, so I thought I'd make this similar to a reference cell in Excel.

3) Number of units in the building * Number of years to be counted as the denominator. For 10170101, this will be 96 * 3 = 288

Denominator = [Number of building units] * [Number of years to be counted] 

4) The first year to be counted, as a check. For 10170101, this will be 2020 - 3 + 1 = 2018First year to be counted = AVERAGE('Year'[Year])-'Units'[Number of years to be counted] + 1

First year to be counted = AVERAGE('Year'[Year])-'Units'[Number of years to be counted] + 1 

How to get the 12,5% in my visual? Thanks again.

Cheers, Elmar

[v-yingjl · Accepted solution]

Hi @elmarhogenboom ,

I have tested it on the previous file that you shared, kept the previously created measures and a calculated column [First year to count], if all torn down and reconstruction is too complicated

Based on these conditions, create another measure:

result =
VAR _units =
    CALCULATE (
        COUNT ( 'Units'[Marktwaarde complex] ),
        ALLEXCEPT ( Units, 'Units'[Marktwaarde complex] )
    )
VAR _yearcount =
    YEAR ( MAX ( 'Hoekstra 2020-06'[Datum uitbeheer] ) )
        - SELECTEDVALUE ( 'Units'[First year to count] ) + 1
VAR x = _units * _yearcount
RETURN
    'Contracts'[Measure] / x

Attached the modified file that you can refer: VGM-data -forum.pbix

Best Regards,
Yingjie Li

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

[elmarhogenboom]

Hi @v-yingjl,

Thanks again for your answer. This works very well. And more importantly: I understand what it does. I'm rapidly learning, thanks to this great forum. Not there yet, but learning 🙂 Wouldn't it be better to also have a measure for the first year to be counted? So as not to have that as a calculated column.

So in the measure, I would have to substitue the SELECTEDVALUE for another VAR, namely _first_year, right? I tried this using the calculated column, but couldn't get it to work. I thought it would be something like

VAR _first_year = 

IF([Year renovation]>2015;{Renovatiejaar];
    IF([Construction year]>2015;[Bouwjaar];2016))

This is the logic of the calculated column, though.

Cheers, Elmar

Cheers, Elmar