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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jamiefisher
Frequent Visitor

Using a date parameter / filter in a visual

I have a table of absences e.g

 

NameStart DateEnd Date
John01/08/2330/08/23
John10/03/2311/03/23
Mark02/08/2304/08/23

 

I want users to be able to select a date e.g. 03/08/23 and to return John and Mark as being absent on this date

 

How best would I do this?

1 ACCEPTED SOLUTION
DOLEARY85
Super User
Super User

Hi,

 

If you are going to use a single value for your date selection you could use something like this:

 

Measure =
    IF (
        SELECTEDVALUE('date'[Date]) >= CALCULATE(MAX('Table'[Start Date]))&&
        SELECTEDVALUE('date'[Date]) <= CALCULATE(MAX('Table'[End Date])),
        "Yes",
        "No"
    )
 
you'll need a separate date table, i've included a PBIX file to review
 
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

View solution in original post

7 REPLIES 7
jamiefisher
Frequent Visitor

Thank you for this - applied it easily to my visuals and works beautifully 👍

Hi I have hit another issue, the measure works fine and displays a "yes" where absences cover the selected dates. I now want to do some counts and calculations but cannot do these on a measure, how do i do this?

Hi,

 

You should still be able to do calculations on a measure, do you have an example of a calculation you are working on with some sample data and i'll have a look at it

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Hi from what you sent me yesterday I need to understand how I go about being able to count or sum from the results of a measure or do I need a new measure whith slightly more complex code?

 

jamiefisher_0-1700671738961.png

 

Ah okay, you should be able to use sumx to create a sum and just adjust the measure slightly, try the code below:

 

SumMeasure =
CALCULATE(
    SUMX(
        'Table (2)',
        IF (
            SELECTEDVALUE('date'[Date]) >= 'Table (2)'[Start Date] &&
            SELECTEDVALUE('date'[Date]) <= 'Table (2)'[End Date],
            1,
            0
        )
    )
)
 
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Perefct - thank you for your prompt help with this! 😊

DOLEARY85
Super User
Super User

Hi,

 

If you are going to use a single value for your date selection you could use something like this:

 

Measure =
    IF (
        SELECTEDVALUE('date'[Date]) >= CALCULATE(MAX('Table'[Start Date]))&&
        SELECTEDVALUE('date'[Date]) <= CALCULATE(MAX('Table'[End Date])),
        "Yes",
        "No"
    )
 
you'll need a separate date table, i've included a PBIX file to review
 
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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