How to duplicate values from grouped measures into additional virtual groups

[cesarvinas]

Hi. I have a table with places and corresponding population per year. Currently I only have data up to year 2018. Somethin like this:

Place	Year	Population
a	2017	12
a	2018	11
b	2017	43
b	2018	21

I've created a measure that sums the "Population" column values and used it in a columns chart with the "Year" column as the axis to see the total population per year. The data in the chart would be:

Year	Total
2017	55
2018	33

I need the chart to also show years 2019 and 2020 with the same value from 2020, like:

Year	Total
2017	55
2018	33
2019	33
2020	33

How can I achieve the above via DAX. I basically need to take the "Total" from the MAX "Year" and dynamically create two additional entries for 2019 and 2020 with the same total from that MAX "Year".

My use case is more involved as then I will have to add another measure to the same columns chart to show how many of that total population attended a particular class. My table with the "class attendance" data does have values up to 2020 which is why I need the total population to go all the way to 2020 and assume that the population for 2019 and 2020 is the same than in 2018 so then I can compare that with class attendance numbers.

Any help or guidance is welcomed.

Thanks.

[amitchandak]

@cesarvinas , You need to have a new table Year table, Using that try to force the data for the year you do not have the year.

Can you share sample data and sample output in table format?

[cesarvinas]

@amitchandak, don't know how to attach pbix files here, but see screenshots of the example I have below. The question is: how do I combine the Years table with the measure to force the additional 2019 and 2020 years with a value same than the total for 2018?

Measure is: TotalPopulation = SUM('PlacePopulation'[Population])

[mahoneypat · Accepted solution]

Now that you have your Years table, you could use an expression like this for your population to get the most recent non-blank population

Population =
VAR __thisyear =
    SELECTEDVALUE ( Years[Year] )
VAR __summary =
    ADDCOLUMNS (
        FILTER ( ALL ( Years[Year] ), Years[Year] <= __thisyear ),
        "@Pop", CALCULATE ( SUM ( PlacePopulation[Population] ) )
    )
VAR __Top1 =
    TOPN ( 1, FILTER ( __summary, [@Pop] > 0 ), Years[Year] )
VAR __result =
    MAXX ( __Top1, [@Pop] )
RETURN
    __result

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[cesarvinas]

@mahoneypat, thank you very much. The solution worked. I didn't reply before because I wanted to complete my use case first base on your helpful answer. I've not been able to though so I thought I would describe the next step here to see if you could help a little bit more.

My full use case includes offices that are linked to my places. Each office has a year when it was opened and a year when it was clossed. I need then to calculate a second measure that sums up the population from PlacePopulation, but this time having into account if there was at least one active office in the place for the place to include in the calculation. See below for updated tables with example data:

1. Place

see how this time a Place has one or more Offices. ParentPlaceId links offices to places. OfficeYearOpen and OfficeYearClose determine the time range an office was active. Also, Places belong to Regions indicated by the Region column.

2.  PlacePopulation

Region is calculated with Power Query and it's basically the value from Place that corresponds to the PlaceId in PlacePopulation. HadOpenOffice is also calculated in Power Query and indicates if for a given Population Year, there was at least one open Office in that year for that Place. For example PlaceId = 1 (PlaceName = a) has 2 offices: a_o1 openes in 2015 and still active (i.e. OfficeYearClosed is BLANK) and a_o2 opened in 2018 and also still active. Then for the entry in PlacePopulation that corresponds to PlaceName = a and years 2017 and 2018, the value for HadOpenOffice is TRUE.

3. Year

Need:

1. Two measures: TotalPopulation and TotalPopulationWithOpenOffice

2. I need to calculate a third measure for %PopulationServed that represents the total population that was served because there was at least one open office

3. I need to be ablet to drill down the 3 measures by year and region.

TotalPopulation:

I've used your DAX code for this and I see it works. When I add it to a Stacked Column Chart and then use Year (from the Years table) as the Axis and Region (from the PlacePopulation table) as the Legend, I can see the correct values are calculated per year and region:

TotalPopulationWithOpenOffice:

From my example data, there is population data only for 2017 and 2018. However, I need to be able to generate values for 2019 and 2020. For 2019 and 2020 I need to use the most recent population data, which in my example is from 2018. However, I have to have into account if there was at least one active office in those years. For 2017 and 2018 I use the calculated HadOpenOffice column to only include the rows from PlacePopulation where HadOpenOffice = TRUE in the SUM calculation. For 2019 and 2020 I need to also identify if there was at least one active office in the Place before including the population value from PlacePopulation in the SUM calculation. Using your DAX code as base, I came up with this:

TotalPopulationWithOpenCenter = 
/*calculate the most current year of available population data*/
VAR MostCurrentPopulationYear = 
    CALCULATE(MAX(PlacePopulation[Year]), ALL(PlacePopulation))
VAR __thisyear = 
    SELECTEDVALUE ( Years[Year] )
VAR __summary =
    ADDCOLUMNS (
        FILTER ( ALL ( Years[Year] ), Years[Year] <= __thisyear ),
        "@Pop", CALCULATE(
                    SUM(PlacePopulation[Population]), 
/*Added filter to only include population values from rows in PlacePopulation with HadOpenOffice = TRUE*/
                    PlacePopulation[HadOpenOffice] = TRUE()
                )
    )
VAR __top1 = 
    TOPN ( 1, __summary, Years[Year], DESC )
VAR __result = 
/*here I try to calculate the number for years 2019 and 2020. If the value for @Pop is BLANK, then that is a year for which there is no data in PlacePopulation*/
    IF(ISBLANK(MAXX(__top1, [@Pop])), CALCULATE(
                                        SUM(
                                            PlacePopulation[Population]
                                        ), 
/*here I apply filters to only use rows from PlacePopulation that are from most current year and have at least one child Office in the Place table that is active (i.e. OfficeYearOpen is less than or equals to the year that is being evaluated (2019 or 2020) and OfficeYearClose is BLANK or greater than or equals to the year that is being evaluated*/
                                        FILTER(
                                            FILTER(
                                                ALL(PlacePopulation), 
                                                PlacePopulation[Year] = MostCurrentPopulationYear
                                            ),
                                            CONTAINS(
                                                FILTER(
                                                    Place, 
                                                    Place[OfficeYearOpen] <= __thisyear && 
                                                        (Place[OfficeYearClose] = BLANK() || Place[OfficeYearClose] >= __thisyear)
                                                ),
                                                Place[Type], "Office", Place[ParentPlaceId], PlacePopulation[PlaceId]
                                            )
                                        )
                                    ), MAXX(__top1, [@Pop]))        
RETURN
    __result

The formula above kind of works. If I only use Year (from the Years table) as the Axis for a Clustered Column Chart:

However, as soon as I convert this into a Stacked Column Chart and add Region (from the PlacePopulation table) as Leged or if I then use the two measures (TotalPopulation and TotalPopulationWithOpenCenter) to calculate a third one with the percentage, the values get messed up. For example, TotalPopulationWithOpenCenter shows the same values for all Regions. TotalPopulation starts to show wrong values for years 2019 and 2020, etc.

I hope I've added enough detail and all is clear. If there is a way I can attach my example PBIX, please let me know and I'll do.

Again, any guidance/help is highly appreciated.

Thanks.

[Icey]

Hi @cesarvinas ,

It is suggested to upload your file to OneDrive for Business and then paste the link here. Please remove sensitive information.

Best Regards,

Icey

[cesarvinas]

Thank you, @Icey. My organization doesn't allow to share through Links so sharing from my personal OneDrive: https://1drv.ms/u/s!AhZX9OMsvgRuiMcYzIJk3lIEfF_0_A?e=RVGc5b.

@mahoneypat, I hope the example PBIX is helpful. Let me know please.  

[cesarvinas]

@mahoneypat, I'm accepting your solution as it did solve my initial need. I've not been able to come up with DAX metrics for the rest of my needs so for now I decided to use Power Query to duplicate the most current year of pouplation data into missing next years up to current one. With that I'm able to build simpler metrics and consumers can drill them now. Thank you for all the help!