Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Consider the below example,
I am having to tables search and search_activitiy
Search table contains following,
id user_id search_date
1 76 2023-01-01
2 88 2023-10-11
Search Activity table contains following,
id search_id search_type availability
1 1 book true
2 1 music true
3 1 news false
3 2 news true
4 2 book false
5 2 music true
I want to count the id in search table if only the search_type music and book are available (Both music and book are available
Here it will count the search as 1.
can anyone help on how to achieve this in DAX?
Note: I want to create a measure and above two tables are related in 1 to many.
Thanks in advance
Solved! Go to Solution.
@SanthiyaMallow , Please try these two measures, 2nd measure is what you need.
M1= calculate(distinctcount(Table[Search Type]), filter(Table, [Search Type] in {"book", "music"}))
Count having both = countx(filter(values(Table[id]), [M1] >=2), [id]
@SanthiyaMallow , Please try these two measures, 2nd measure is what you need.
M1= calculate(distinctcount(Table[Search Type]), filter(Table, [Search Type] in {"book", "music"}))
Count having both = countx(filter(values(Table[id]), [M1] >=2), [id]
User | Count |
---|---|
99 | |
86 | |
80 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |