Forum Discussion

Divous's avatar
Divous
Helper III
4 years ago
Solved

Show values based on filter and text string

Hi community,

 

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

 

I want to show only rows per customer_id who have size big in specific date using date selection. So when I use date slicer for 12. 2. 2022 it show only this table:

2 11.02.2022 color blue
2 12.02.2022 size big
2 13.02.2022 price 200

 

I am trying to make an IF statement with true/false which I can use in date slicer in filter visual. 

 

Customer_have_size_big = IF(FILTER(VALUES('Table01'[customer_id]),CONTAINSSTRING('Table01'[info],"big")),0,1)

 

 

But still without a luck. May I ask you for help?

 

Thanks

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Divous ,

     

    Please create the measure and filter the visual with measure.

     

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

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Divous , Make sure you use independent date table in slicer

     

    //Date1 is independent Date table
    new measure =
    var _max = maxx(allselected(Date1),Date1[Date])
    var _tab = summarize(filter(all(Table),Table[date] =_max), [customer_id])
    return
    calculate(count(Table[customer_id]), filter(Table, Table[customer_id] in _tab))

    • Divous's avatar
      Divous
      Helper III
      4 years ago

      Hi amitchandak 

      thanks for your response.

      I create independent Date table and try to use your measure, but probably I dont know how.


      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        4 years ago

        Hi Divous ,

         

        Please create the measure and filter the visual with measure.

         

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

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
        Best Regards,
        Winniz
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.