Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
73 | |
55 | |
36 | |
34 |
User | Count |
---|---|
99 | |
56 | |
53 | |
44 | |
40 |