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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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