DAX Help: Creating dynamic measures using dataset

[Azhar]

Hi,

I have a dataset with columns for different projects that have values per year, please see table below:

Project#	Years	Max of Value
Project 1	2017	60
Project 2	2017	60
Project 1	2018	497
Project 2	2018	417
Project 1	2019	893
Project 2	2019	496
Project 3	2019	107
Project 1	2020	873
Project 2	2020	357
Project 3	2020	1 086
Project 1	2021	238
Project 2	2021	-
Project 3	2021	531

I have created a Power BI report that has a filter for the project and a stacked area graph for values over time. I wanted to create 2 cards:

1. Sum of Peak Value - this looks at each year and returns the year with the max sum. Eg. year 2020 peaks at 2315 (873+357+1086)

I achieved this by duplicating my data set and removing the Project column. This left unique years and its corresponding summed values. I merely used the "MAX" formula in a measure.

1. Year at which Peak occurs - returns the corresponding year at which the value peaks

I achieved this using the "Lookupvalue" formula in a measure using the measure created in point 1 above as reference to return the corresponding year.

I realised that since i removed any reference to the project in the duplicate data set to calculate the measures, it removed all possibility of the measured filtering when i filter the projects. Could you please assist me which an alternate way of completing this calculation whilst still keeping the filtering functionality.

Please see link to dashboard below (i will grant access to those who request):

https://app.powerbi.com/groups/me/dashboards/10b09491-f74f-4b77-8503-c1825932a451

[v-jiascu-msft · Accepted solution]

@Azhar

Hi,

Please try these two measures.

PeakValueMeasure =
MAXX (
    SUMMARIZE (
        'Table1',
        'Table1'[Years],
        "MaxValue", SUM ( 'Table1'[Max of Value] )
    ),
    [MaxValue]
)

PeakYearMeasure =
VAR MaxV = [PeakValueMeasure]
RETURN
    CALCULATE (
        MAX ( 'Table1'[Years] ),
        FILTER (
            SUMMARIZE (
                'Table1',
                'Table1'[Years],
                "MaxValue", SUM ( 'Table1'[Max of Value] )
            ),
            [MaxValue] = MaxV
        )
    )

Best Regards!

Dale

[Azhar]

@v-jiascu-msft Thank you - works perfectly! much appreciated.

Could you recommend any good DAX training?

[v-jiascu-msft]

@Azhar

Hi,

I am so glad it helped. It's my pleasure. http://www.sqlbi.com/ is a wonderful place to learn DAX, which is created by senior professionals. The official reference is also very good. When you get an idea, you can find a function here to achieve it. Here is it: https://msdn.microsoft.com/en-us/library/ee634396.aspx. 

Wish you a happy journey to learn.

Best Regards!

Dale