Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I'm looking for help on something that looks easy but I can't wrap my head around.
I have a table with 2 columns with consumer ids and the type of product they bought, as shown on the following table.
Consumer id | Type |
1 | apple |
2 | apple |
2 | pear |
3 | pear |
4 | pear |
4 | carrot |
4 | apple |
5 | carrot |
6 | carrot |
I'm looking for a Dax formula to quickly retrive the count of consumers who have bought both an apple & a pear, so in this case only id 2 & 4.
Thanks for your help
Solved! Go to Solution.
Generic solution
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Generic solution
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Try this:
Count =
VAR Id_ = 'Table'[Consumer id]
return
IF( CALCULATE( COUNTROWS('Table') ,'Table'[Consumer id] = Id_ ,'Table'[Type] = "Apple" || 'Table'[Type] = "Pear" ) = 2 , 1 , BLANK() )
@Anonymous , You can try solution by @mh2587 , But I think it should be like below if you need both
countx(filter(addcolumns(summarize(Table, Table[Consumer id]) , "_apple", countx(filter(Table, Table[Type] ="apple"),[Consumer id] )
, "_pear", countx(filter(Table, Table[Type] ="pear"),[Consumer id] ) ), not(isblank(_apple)) && not(isblank(_pear)) ),[Consumer id])
=Calculate(COUNT[Consumer ID],[Type] IN {"apple","pear"})
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |