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
oceany
Frequent Visitor

Best way to see filtered rows of a table when the filter is compared with a slicer value

Hi! 

I have a tableA that has 'start date' and 'finish date' columns.

Let us say tableA looks like this

Row Numberstart datefinish date
12022-01-012022-02-15
22022-02-012022-08-01
32022-02-142022-03-01
42022-03-012022-03-15
52022-02-272022-05-01

 

I would like to allow a user to select a date among these dates [2022-01-31, 2022-02-28, 2022-03-31], so I created a tableB with a column EndofMonth, and used it as a slicer. (I thought I could use SELECTEDVALUE('tableB'[EndofMonth]).

Now if a user selects 2022-02-28 in the slicer, I would like to show this table below. Basically, it is filtered where start date>= selected value && finish date<= selected value. I tried to created a calculated column which looks like col = CALCULATE(TABLE, FILTER( ,[start date>=selected value && [finish date<=selected value)), but it returns blanks values. 

Row Numberstart datefinish date
22022-02-012022-08-01
32022-02-142022-03-01
52022-02-272022-05-01

 

Is there a way to do this using a slicer? Thank you!

 

 

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

Hi, @oceany 

According to your description, you want to create a month-end table as a slicer, and then use the slicer to filter the original table.. Right?

Here are the steps you can follow:

(1)This is my test data: Test

vyueyunzhmsft_0-1662615511801.png

 

(2) We can click "New Table" and enter DAX to automatically create our month-end table:

 

End of month =
DISTINCT (
    SELECTCOLUMNS (
        ADDCOLUMNS (
            CALENDAR ( FIRSTDATE ( 'Test'[start date] ), LASTDATE ( 'Test'[finish date] ) ),
            "end", EOMONTH ( [Date], 0 )
        ),
        "end of month", [end]
    )
)

 

vyueyunzhmsft_1-1662615511803.png

 

(3)Then we can create a measure : isDisplay

 

isDisplay =
VAR _slice =
    SELECTEDVALUE ( 'End of month'[end of month] )
RETURN
    IF (
        ISFILTERED ( 'End of month' ),
        IF (
            MAX ( 'Test'[start date] ) <= _slice
                && MAX ( 'Test'[finish date] ) >= _slice,
            1,
            0
        ),
        1
    )

 

(4)We can put the [end of month] column in the slice, and the fields we need in the table.After it , we need to put the [isDisplay] measure in the “Filter on this visual” and configure it like this :

vyueyunzhmsft_2-1662615511810.png

 

Then we can meet your need now.

 

If this method can't meet your requirement, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

View solution in original post

4 REPLIES 4
v-yueyunzh-msft
Community Support
Community Support

Hi, @oceany 

According to your description, you want to create a month-end table as a slicer, and then use the slicer to filter the original table.. Right?

Here are the steps you can follow:

(1)This is my test data: Test

vyueyunzhmsft_0-1662615511801.png

 

(2) We can click "New Table" and enter DAX to automatically create our month-end table:

 

End of month =
DISTINCT (
    SELECTCOLUMNS (
        ADDCOLUMNS (
            CALENDAR ( FIRSTDATE ( 'Test'[start date] ), LASTDATE ( 'Test'[finish date] ) ),
            "end", EOMONTH ( [Date], 0 )
        ),
        "end of month", [end]
    )
)

 

vyueyunzhmsft_1-1662615511803.png

 

(3)Then we can create a measure : isDisplay

 

isDisplay =
VAR _slice =
    SELECTEDVALUE ( 'End of month'[end of month] )
RETURN
    IF (
        ISFILTERED ( 'End of month' ),
        IF (
            MAX ( 'Test'[start date] ) <= _slice
                && MAX ( 'Test'[finish date] ) >= _slice,
            1,
            0
        ),
        1
    )

 

(4)We can put the [end of month] column in the slice, and the fields we need in the table.After it , we need to put the [isDisplay] measure in the “Filter on this visual” and configure it like this :

vyueyunzhmsft_2-1662615511810.png

 

Then we can meet your need now.

 

If this method can't meet your requirement, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

You are a genius👍!!! Thank you!

Thanks for your answer! I saw the DAX function below from the example you shared. How would I modify that one to filter my table? I need to show the same columns of the tableA, not countx. Also, can I use SELECTEDVALUE('tableB'[EndofMonth]) in the formula?

Day by Month = CALCULATE(countx(SUMMARIZE(filter(CROSSJOIN('Data','Date'),'Date'[Date] >= 'Data'[StartDate] && 'Date'[Date]<= 'Data'[EndDate]),'Data'[id],'Date'[Date]),'Date'[Date]))

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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