topic Re: Create subset of a table based on filter slicer in DAX Commands and Tips

Create subset of a table based on filter slicer

ThomasSan — Fri, 22 Mar 2024 08:42:23 GMT

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:

Customer	Attribute A	Attribute B	Date Since	Date Until
A	Yes	qrv	01.11.2021
B	No	abd	05.09.2023
C	No	bbb	20.09.2023
A	No	qrv	01.01.2016	31.10.2021
B	No	abc	01.03.2019	05.09.2023
C	Yes	abc	23.04.2019	19.09.2023
D	Yes	qrv	27.03.2020	09.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

Customer	Attribute A	Attribute B
A	Yes	qrv
B	No	abc
C	Yes	abc

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

Re: Create subset of a table based on filter slicer

lbendlin — Fri, 22 Mar 2024 19:32:11 GMT

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.

Re: Create subset of a table based on filter slicer

Anonymous — Mon, 25 Mar 2024 07:10:59 GMT

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.

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.