Forum Discussion

Divous's avatar
Divous
Icon for Helper III rankHelper III
4 years ago
Solved

Show values based on date and CONTAINSSTRING

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
108.01.2022 color red
109.01.2022 size small
110.01.2022 price100
211.02.2022 color blue
212.02.2022 size big
213.02.2022 price200
314.03.2022 color pink
315.03.2022 size middle
316.03.2022 price300
417.03.2022colororange
418.03.2022sizebig
419.03.2022price400
520.03.2022colorblack
521.03.2022sizebigger
522.03.2022price500

 

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.