Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JustinDoh1
Post Prodigy
Post Prodigy

Trying to add one more condition on this measure

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.)

JustinDoh1_0-1728605522108.png

 

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:

JustinDoh1_2-1728606037537.png

 

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

In this case, it would be September 2024. 

 

JustinDoh1_0-1728607373581.png

 

 

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:

JustinDoh1_3-1728606946814.png

 

For Avi:

Input: 52 --> 3 (forecast star)

           53 --> 2

           54 --> 2

 

Thank you!

1 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
            )
        )
)

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

7 REPLIES 7
JustinDoh1
Post Prodigy
Post Prodigy

@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!

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
            )
        )
)

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

@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. 

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

@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
Super User
Super User

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
            )
        )
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

@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!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors