Reply
ThomasSan
Helper IV
Helper IV

Create subset of a table based on filter slicer

Hi everyone,

 

I would like to create a subset of a larger table based on date parameters I would insert via a filter. 

 

My main table has the structure as seen here:

CustomerAttribute AAttribute BDate SinceDate Until
AYesqrv01.11.2021 
BNoabd05.09.2023 
CNobbb20.09.2023 
ANoqrv01.01.201631.10.2021
BNoabc01.03.201905.09.2023
CYesabc23.04.201919.09.2023
DYesqrv27.03.202009.10.2021

If there is no value in the column Date Until, it means that this entry is still current and the customer is active (unlike customer D, for example)

 

Now, let's say the user would select the date December 11 2021 via the filter. I would now like to have a subset of the table above which would be, given the selected date, be 

CustomerAttribute AAttribute B
AYesqrv
BNoabc
CYesabc

 

The reason why I would like to have a subset of the table above is that I would like to use the different columns of this subset of the main table to use in other measures. 

Can anyone help me in finding the right measure to create the subset as described above?

 

Currently, I tried the following measure but I am not quite sure whether or how I can even access the different columns of the variable subsets in other measures

 

 

 

 

Subset = 
var subsets=
FILTER(
    maintable,
    maintable[Date Since]>Date(2021,12,11) && maintable[Date Until]<Date(2021,12,11) 
)

var colum=
COUNTROWS(
    subsets
)

RETURN
colum

 

 

 

 

 

 

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

Hi @ThomasSan 

 

Thanks @lbendlin  for your timely reply. Here are my additions:

 

You can create a measure as follows.

1 =
IF (
    MAX ( [Date Since] ) <= SELECTEDVALUE ( 'Date'[Date] )
        && MAX ( [Date Until] ) >= SELECTEDVALUE ( 'Date'[Date] )
        || MAX ( [Date Since] ) <= SELECTEDVALUE ( 'Date'[Date] )
            && MAX ( [Date Until] ) = BLANK (),
    1,
    0
)

 

Put the measure into the filter so that the visual only shows data where the measure is equal to 1.

vxuxinyimsft_0-1711350561143.png

 

Is this the result you expect?

 

Best Regards,
Yulia Xu

 

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

2 REPLIES 2
v-xuxinyi-msft
Community Support
Community Support

Hi @ThomasSan 

 

Thanks @lbendlin  for your timely reply. Here are my additions:

 

You can create a measure as follows.

1 =
IF (
    MAX ( [Date Since] ) <= SELECTEDVALUE ( 'Date'[Date] )
        && MAX ( [Date Until] ) >= SELECTEDVALUE ( 'Date'[Date] )
        || MAX ( [Date Since] ) <= SELECTEDVALUE ( 'Date'[Date] )
            && MAX ( [Date Until] ) = BLANK (),
    1,
    0
)

 

Put the measure into the filter so that the visual only shows data where the measure is equal to 1.

vxuxinyimsft_0-1711350561143.png

 

Is this the result you expect?

 

Best Regards,
Yulia Xu

 

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

lbendlin
Super User
Super User

let's say the user would select the date December 11 2021 via the filter. I would now like to have a subset of the table

 

That's it. That's all you need.  Applying a filter will give you the subset of the table.

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)