Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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.
@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))
Hi @amitchandak
thanks for your response.
I create independent Date table and try to use your measure, but probably I dont know how.
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.
Hi @v-kkf-msft Winniz,
thanks for your response!
It looks great!
But, I have found that I need little polishing. I have in the "info" column not only the "big" itself, but it can also be in a sentence. For example, "he wants a big size." So I need to filter that too.
EDIT:
I use CONTAINSTRING in part of checking "big" and its working.
Winniz, everything is OK and working.
Thanks a lot!