Trying to add one more condition on this measure

[JustinDoh1]

I have bottom measure that I came up from help of the community back in 2021.

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Filter-option-in-Calculate-to-take-M...

I recently realized that I am missing one more criteria.

I have uploaded my PBIX file here.

Basically, bottom is screenshot of a measure:

(sorry for such a weird long name of table (vw_MxTtWghtdHlthSrvyScr) as I tried to rename, but I got error so I could not change.)

When I got the measure back in 2021, I did not think that this only retrieves one result out of all data, not filtering current month.

This is where this measure is located in the file:

Basically, I need to add one more criteria to get the result from Selected month:

In this case, it would be September 2024. 

How can you add that criteria to the existing measure?

So, if the measure is modified, I should see 2 for the result on the bottom:

For Avi:

Input: 52 --> 3 (forecast star)

           53 --> 2

           54 --> 2

Thank you!

[JustinDoh1]

@Jihwan_Kim 

I would like to adjust my requirement little bit (Sorry).

Instead of using Max, how can we adjust to "selected ProcessingDate"?

(So, if use select August 2024, it would take the value from that month, so forth.)

I thought MAX would solve the issue, but I think there is other scenario that it would not work.
Thank you for help again!

[Jihwan_Kim · Accepted solution]

Hi,

I saw that the slicer on the page is set up as "single selection". So, I think, it still shows the same result when you try to use SELECTEDVALUE DAX function.

SELECTEDVALUE function - DAX | Microsoft Learn

I quite do not understand about the meaning of "the other scenario".

Perhaps, if you are describing about selecting multiple dates from the slicer, please try to write something like below, and try to use IN operator in the formula.

DAX operators - DAX | Microsoft Learn

ForecastStar =
IF (
    HASONEVALUE ( 'Table'[Provider Name] ),
    VAR CurrentTestValue = forecast[Forecast]
    VAR _processingdate =
        VALUES ( 'Date Bridge'[ProcessingDate] )
    RETURN
        CALCULATE (
            SELECTEDVALUE ( vw_MxTtWghtdHlthSrvyScr[HealthInspectionRating] ),
            TOPN (
                1,
                FILTER (
                    ALL ( vw_MxTtWghtdHlthSrvyScr ),
                    CurrentTestValue <= vw_MxTtWghtdHlthSrvyScr[MaxTotalWHSS]
                        && vw_MxTtWghtdHlthSrvyScr[ProcessingDate] IN _processingdate
                ),
                vw_MxTtWghtdHlthSrvyScr[MaxTotalWHSS], ASC
            )
        )
)

[JustinDoh1]

@Jihwan_Kim I have a question. You mentioned that you were using SELECTEDVALUE. 

But what you have added was not using SELECTEDVALUE, so I am wondering what you meant by.

I had SELECTEDVALUE earlier.

It was using just "Values", no?

Thanks. 

[Jihwan_Kim]

Hi,

SELECTEDVALUE DAX function returns value.

SELECTEDVALUE function - DAX | Microsoft Learn

VALUES DAX function returns a column(or a list) or a table.

VALUES function (DAX) - DAX | Microsoft Learn

If only one value is selected from the slicer (in your sample), I think selectedvalue dax fuction is OK to use.

Use SELECTEDVALUE instead of VALUES in DAX - DAX | Microsoft Learn

IF more than one values are selected from the slicer (I misunderstood your second question when you mentioned about "other scenario"), it is good to use IN operatoer with VALUES or DISTINCT or other dax function that returns one column or a list.

I hope this helps. 🙂
Thank you.

[JustinDoh1]

@Jihwan_Kim Thank you so much again.

I will review more closely on Monday, and let you know.

Regards to "other scenario", I meant "if I select other month, beside Sept 2024".

I think this measure should work, but I want to make sure.

Thank you so much!

[Jihwan_Kim]

Hi,

I don't fully understand the logic behind the measure, but please try something like below if you want to add one more condition that is coming from the date bridge processing date slicer.

ForecastStar =
IF (
    HASONEVALUE ( 'Table'[Provider Name] ),
    VAR CurrentTestValue = forecast[Forecast]
    VAR _processingdate =
        MAX ( 'Date Bridge'[ProcessingDate] )
    RETURN
        CALCULATE (
            SELECTEDVALUE ( vw_MxTtWghtdHlthSrvyScr[HealthInspectionRating] ),
            TOPN (
                1,
                FILTER (
                    ALL ( vw_MxTtWghtdHlthSrvyScr ),
                    CurrentTestValue <= vw_MxTtWghtdHlthSrvyScr[MaxTotalWHSS]
                        && _processingdate = vw_MxTtWghtdHlthSrvyScr[ProcessingDate]
                ),
                vw_MxTtWghtdHlthSrvyScr[MaxTotalWHSS], ASC
            )
        )
)

[JustinDoh1]

@Jihwan_Kim I think your solution is correct. I need to test more tomorrow, but so far it looks fine. I will update the status tomorrow. Thank you!