Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have bottom measure that I came up from help of the community back in 2021.
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!
Solved! Go to 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.
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.
@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.
@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!
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.
@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!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
20 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
37 | |
25 | |
24 | |
22 |