Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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]
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
85 | |
85 | |
69 | |
67 | |
63 |
User | Count |
---|---|
213 | |
124 | |
117 | |
81 | |
75 |