Apply related value of Processing Date (month)

[JustinDoh1]

I am sharing Pbix file here (Google drive) for you to take a look.

Basically, I need to fix this DAX code or do something to apply value that is related to different Processing Date.

I have three processing dates (April, May and June 2021).

This is DAX code that I had originally usingTOPN function, but I cannot apply this any longer, but retrieve the value based on Processind Date.

So, I need a guidance to modify this code.

Currently, the calculation of "MaxPtsHighestPotnetialStars" is using only one value (April 2021's value = 78.67 because of ASC) of MaxTotalWHSS instead of applying thru different month's value.

Where or how do I apply in DAX to follow the Processing Date for that particular month?

I have created a third table between two tables for the ProcessingDate.

Expected outcomes are:

April:  78.67

May: 79.33

June: 79.33

Currently all same (wrong):

April:  78.67

May: 78.67

June: 78.67

I have tried as below, but no luck yet:

CALCULATE

(

SELECTEDVALUE( vw_MxTtWghtdHlthSrvyScr[MaxTotalWHSS]),

            //TOPN (1,

            FILTER (

ALL(vw_MxTtWghtdHlthSrvyScr),

CurrentTestValue <= vw_MxTtWghtdHlthSrvyScr[MaxTotalWHSS]

&& vw_MxTtWghtdHlthSrvyScr[ProcessingDate] = SELECTEDVALUE('Date Bridge'[ProcessingDate])

                )

//vw_MxTtWghtdHlthSrvyScr[MaxTotalWHSS]

                //DESC

                    //)

             )

[daxer-almighty · Accepted solution]

@JustinDoh1 

Sorry, I had to rename your tables because the names are horrible. A hint: You should never expose such ugly things to the users of your reports. Measures which are NOT to be hidden must have good names as well. With spaces between words. You are allowed to only use ugly names (but you shouldn't!) for objects that are going to be hidden from view. But even those should be decent pronouncable names. There are many reasons behind this. So, please whatever you do---even if it's a draft---use the rules. You'll thank me later.

MaxPotStarsWZeroPt = 
IF( HASONEVALUE( 'Provider Info'[Provider Name]),
    var CurrentTestValue = [SUM C1-C3]
    return
        CALCULATE(
            // The value returned by SELECTEDVALUE will
            // not be BLANK as long as TOPN returns
            // only 1 row/value. This means MaxTotlaWHSS
            // must be unique in the current context,
            // which in turn means that in the current
            // selected period there should NOT exist
            // 2 equal values of MaxTotalWHSS. If you
            // can guarantee this, you'll get the rating,
            // otherwise you'll get BLANK.
            SELECTEDVALUE( Survey[HealthInspectionRating]),
            TOPN(1,
                FILTER(
                    ALL( Survey[MaxTotalWHSS] ),
                    CurrentTestValue <= Survey[MaxTotalWHSS]
                ),
                Survey[MaxTotalWHSS],
                ASC
            ),
            ALL( Survey )
        )
)

MaxPtsHighestPotentialStars = 
SWITCH( TRUE(),
    [MaxPotStarsWZeroPt] = 1, "*",

    HASONEVALUE( 'Provider Info'[Provider Name]),
        var CurrentTestValue = [SUM C1-C3]
        var Result =
            // Tables with one column and one row
            // only are automatically converted into
            // a scalar value. If the function returns
            // more than 1 row, you have to revise the
            // logic and the data in your table.
            TOPN (1,
                FILTER (
                    // You've got to use DISTINCT or VALUES
                    // (read about the differences between these two
                    // functions @ dax.guide) to keep the filter
                    // on dates intact. By applying ALL you remove
                    // all filters from an expanded(!!!) table or a
                    // column depending on what argument you supply
                    // to the function.
                    DISTINCT( Survey[MaxTotalWHSS] ),
                    CurrentTestValue <= Survey[MaxTotalWHSS]   
                ),                                
                Survey[MaxTotalWHSS],                                
                ASC
            )
        return
            Result
)

As you can see from my code... I hate carelessness.

I hope the above does what you want.

[JustinDoh1]

@daxer-almighty  Thank you so much for your help and your advice regards to naming.

[daxer-almighty]

You've created an ordinary date/time dimension, not a bridge table. A bridge table is one that links two dimensions that have a many-to-many relationship.

I wish I could help you but I just don't get the description... It's kind of complicated.

[JustinDoh1]

@daxer-almighty Thanks for your comment. I researched more for the issue and modified the original post and Pbix file to be simplier. It appears that I need to fix DAX. How do I points to a particular month? Kindly appreciated for help.