Summarizing & reworking Data

Hi Anonymous ,

You can do this without the  need for the Ageband table.

On your Database unpivot your two Age columns this will give you two columns one witht the Attribute and value.

Create a new table with the age band you need:

AgeBandMinimumMaximum

Now create the following measure:

Count by age =
VAR temnp_table =
    FILTER (
        DataBase_Unpivot,
        DataBase_Unpivot[Value] <= MAX ( Ageband[Maximum] )
            && DataBase_Unpivot[Value] >= MIN ( Ageband[Minimum] )
    )
RETURN
    COUNTROWS ( temnp_table )

If the Unpivot of the columns is not an option beside the age band table create one for the ages:

Ages

Now create the followin measure:

Count by age no unpivot =
SWITCH (
    SELECTEDVALUE ( Ages[Ages] ),
    "Age1",
        COUNTROWS (
            FILTER (
                DataBase,
                Database[Age] <= MAX ( Ageband[Maximum] )
                    && Database[Age] >= MIN ( Ageband[Minimum] )
            )
        ),
    "Age2",
        COUNTROWS (
            FILTER (
                DataBase,
                Database[Age 2] <= MAX ( Ageband[Maximum] )
                    && Database[Age 2] >= MIN ( Ageband[Minimum] )
            )
        )
)

Check PBIX file with both options

I have kept your model for comparision but has refered no need to keep the age bands for both ages, also be aware that the two tables are not related with the other tables.