cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
clachi80
Frequent Visitor

Use slicer selection in a function

I have created two slicers both listing all hours in a day populated from two tables with one column

The user must select one hour in each slicer: one indicating the starting hour and one the ending hour

Based on the selection I should be able to calculate the number of hours between the two selections and finally use this value in the following function that is reported in a column of a different table. 

 

MidDaysSecDiff =
IF (
FORMAT ( WorkHour[FirstMail], "Short Date" )
<> FORMAT ( WorkHour[LastMail], "Short Date" ),
3600 * 10
* (
CALCULATE (
DISTINCTCOUNT ( 'Calendar'[Date] ),
FILTER (
'Calendar',
'Calendar'[Date] > WorkHour[FirstDayEndTime]
&& 'Calendar'[Date] < WorkHour[LastDayStartTime]
&& 'Calendar'[WorkDay] = TRUE ()
)
)
- 1
),
0
)

 

(the difference between the two selected hours will replace the number "10" in "3600*10"

 

I have tried using DATEDIFF(SELECTEDVALUE(StartSlicer), SELECTEDVALUE(EndSlicer), HOUR) without success

I have also tried to create a new measure that returns the difference of the two values or create a variable but I am not able to retrieve that value in the above function

Could you please tell if and how can I get the desired value in the function?

3 REPLIES 3
mahoneypat
Super User IV
Super User IV

You could make two What If Parameters from 1-24 (hours); see the Modeling tab.  That will make two new tables and add slicers to the report.  There will also be measures created to harvest the selected values.  You can then add a variable to your measure that calculates the difference between those two whatif measures.

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


I've also created two tables through the What if parameter and added a measure with a variable that returns the time difference between the two hours selected in the two slicers... and it worked, I'm able to see the correct value on a card

My problem is that I don't know how (and if possible) to use this value in a function in a computed column like the one indicated in my post. How can I use my VAR in this function instead of the number 10?

I have also created two tables via What if parameter and added a measure with a variable that returns the time difference between the two hours selected in the two slicers... and it worked, I am able to see the correct value in a card

My problem is that I don't know how (and if it is possible) to use this value in a function in a calculated column like the one reported in my post. How can I use my VAR in this function instead of the number 10?

 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors