Show values based on date and CONTAINSSTRING

[Divous]

Hi community,

may I ask you to help me with improving with my DAX formula?

I have Table01 like this:

customer_id	date	details	info
1	08.01.2022	color	red
1	09.01.2022	size	small
1	10.01.2022	price	100
2	11.02.2022	color	blue
2	12.02.2022	size	big
2	13.02.2022	price	200
3	14.03.2022	color	pink
3	15.03.2022	size	middle
3	16.03.2022	price	300
4	17.03.2022	color	orange
4	18.03.2022	size	big
4	19.03.2022	price	400
5	20.03.2022	color	black
5	21.03.2022	size	bigger
5	22.03.2022	price	500

My goal was to show only rows according to customer_id where size contain the word "big".

I have independent date table in slicer and this formula:

Measure = 
VAR SelectDate =
    SELECTEDVALUE ( Dates[Date] )
VAR _id =
    CALCULATETABLE (
        VALUES ( Table01[customer_id] ),
        FILTER (
            ALLSELECTED ( Table01 ),
            Table01[date] = SelectDate
                && CONTAINSSTRING('Table01'[info],"big")
        )
    )
RETURN
    IF (
        SelectDate = BLANK (),
        1,
        COUNTROWS ( INTERSECT ( VALUES ( Table01[customer_id] ), _id ) )
    )

Then this measure I use as a filter in table. 
Everything working fine:

But this work only on specific date.

Now what improvement I need is to make it work when slicer is set on between. So output will show this:

Can you help me with this, please?

Thanks in advance

Divous

[Anonymous · Accepted solution]

Hi @Divous ,

Here's my solution.

There's no relationship between two tables.

Create a measure and add it in to the visual level filters.

Measure =
IF (
    CALCULATE (
        MAX ( 'Table01'[customer_id] ),
        FILTER (
            ALLSELECTED ( 'Table01' ),
            [customer_id] = MAX ( 'Table01'[customer_id] )
                && [date] >= MIN ( 'Date'[Date] )
                && [date] <= MAX ( 'Date'[Date] )
        )
    )
        = MAX ( 'Table01'[customer_id] ),
    1
)

Best Regards,

Stephen Tao

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

[johnt75]

If you have a relationship from the 'Dates'[Date] column to Table01[date] then the filtering should happen automatically. Just remove the SelectDate section from the filter in your measure and it should be fine