DAX query

[BastiaanBrak]

Hello all, I'm hoping someone can help me with a DAX query that works fine in most use cases but not when there is missing data.

To clarify, my data model includes a fact table with rainfall data for different Regions, Years and Time periods and dimension tables for the Regions, Years and Time periods. I've created a DAX measure that calculates mean Rainfall for 1981-2010 and this measure calculates the average if one Time period is selected and sums the averages if more than one Time period is selected: 

Rainfall (mm, 1981-2010 average / sum) =

VAR TimeperiodAVG =

CALCULATE (

AVERAGE('Data'[Rainfall (mm)]),

FILTER(ALLEXCEPT('Data','Regions'[Region abbrev],'Time periods'[Time period]), 'Data'[Year] > 1980 && 'Data'[Year] < 2011))

RETURN

IF(HASONEVALUE('Time periods'[Time period]),

TimeperiodAVG,

SUMX(VALUES('Time periods'[Time period]), TimeperiodAVG)

)

This works fine as long as a particular year has data for all Time periods selected. For example, screenshot below shows that when Time periods January - June have been selected, the measure correctly calculates the same total in every year: 

However, when an additional Time period, here July, is selected for which there is no data in table, the measure no longer calculates the correct total. Instead, the calculation that is performed becomes: 446.1 * (6/7) = 382.4 :

What I would like the measure to calculate instead in this use case (i.e. when Jan - Jul have been selected) is the mean (total) rainfall based on 1981-2010 Years but only for those Time periods selected in the filter for which there is data for a particular year, i.e. in this case Jan - Jun for 2020 and Jan - Jul for all other years. How do I amend the measure above to achieve this?

Many thanks, Bastiaan

.pbix workbook available here (until 15 July 2020): https://filebin.net/oabnasrc12lfnttn

[amitchandak]

@BastiaanBrak , Any reason to put a filter for year separately, is slicer not working?

Try to make it working using slicer. All and selected will rollup values .

Other way, in case you want to put year filter in filter clasue

https://www.youtube.com/watch?v=duMSovyosXE

[BastiaanBrak]

@amitchandak thanks for your response, I've watched the video by BI Elite but I'm afraid I don't see how it solves the problem with my DAX query. Could you clarify please?

"Any reason to put a filter for year separately, is slicer not working?"

The background is that in my actual report (see first report on page here: https://ahdb.org.uk/knowledge-library/weatherhub) I need to compare rainfall in given year and time period(s) against the mean (for same time period(s)) over a long reference period, in this case 1981-2010, hence why these years are added to filter.

[v-yingjl · Accepted solution]

Hi @BastiaanBrak ,

Since you have determined the year range, I don't think you need to create a year slicer any more if you want year 2020 to show the same value as other years when it has some no value in it.

I have modified your formula like this:

Rainfall (mm, 1981-2010 average / sum) = 
VAR TimeperiodAVG =
    CALCULATE (
        AVERAGE ( 'Data'[Rainfall (mm)] ),
        FILTER (
            ALLEXCEPT ( 'Data', 'Regions'[Region abbrev], 'Time periods'[Time period] ),
            'Data'[Year] > 1980
                && 'Data'[Year] < 2011
        )
    )
VAR _mul =
    SUMX (
       FILTER(
           ALLSELECTED('Data'),
           'Data'[Year] > 1980 &&
           'Data'[Year] < 2011
       ),
        [Rainfall (mm)]
    )
        / CALCULATE (
            DISTINCTCOUNT ( Data[Year] ),
            FILTER ( ALLSELECTED ( Data ), 'Data'[Year] > 1980 && 'Data'[Year] < 2011 )
        )
RETURN
    IF ( HASONEVALUE ( 'Time periods'[Time period] ), TimeperiodAVG, _mul )

Not to add year slicer will get the right result, when you use year slicer additionally, when you change the slicer, the measure value will also change.

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.

[BastiaanBrak]

Many thanks @v-yingjl,

I accepted your reply as solution last week hoping I could work out myself how to amend it to accommodate the presence of a Year slicer but so far I've not succeeded.

In the .pbix test file I filtered Years to 1981 - 2020 in PowerQuery (for simplicity I thought, I now realise I shouldn't have done that) but I want my actual report to include a Year slicer so that users can filter to, say, last 10 years without having to use Horizontal scroll bar on a Line chart visual if a different Region or Time period(s) are selected.

See screenshot below as illustration + link below that for published report. Can you advise me how to amend your earlier solution in the presence of a Year slicer?

Kind regards, Bastiaan

https://app.powerbi.com/view?r=eyJrIjoiMzc5OTQzNWQtNjdhMi00MzRkLWJhODItMmNhMTEyMDI2MjM2IiwidCI6ImExM...