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.
Hello everyone.
I have the following table (Table1):
Name | Order Date | Day | Month | Store |
Jhon | 2022/06/01 | 1 | 6 | TV |
Jhon | 2022/06/01 | 1 | 6 | TV |
Jhon | 2022/07/01 | 1 | 7 | TV |
Kelly | 2022/06/01 | 1 | 6 | Phone |
Kelly | 2022/06/01 | 5 | 6 | Phone |
Fred | 2022/06/01 | 1 | 6 | TV |
Fred | 2022/06/01 | 1 | 6 | Phone |
Mark | 2022/06/05 | 5 | 6 | TV |
Mark | 2022/06/05 | 5 | 6 | Xbox |
Jes | 2022/08/12 | 12 | 8 | TV |
So, in a variable using calculatedtable, I look for the values (Names) to be saved, which have bought two or more times in Store=TV or Xbox , in month six. The expected result, taking the table above, would be:
Jhon |
Mark |
What I tried, creating another table and trying to "simulate" the values that said variable would bring, was:
CALCULATEDTABLE(VALUES(Table1[Name]),
NOT(Table1[Store]) = "Phone",
Table1[Month]=6,
FILTER(ALL(Table1), CALCULATE(COUNTROWS(Table1), ALL(Table1), VALUES(Table1[Name]))>=2)
But without result (it kept bringing me all the values of month 6, regardless of the number of occurrences of "Name"). If anyone knows how to do it, I'd appreciate it 🙂
PS: for those who want to know the context of this:
My idea is, for example, in variable1, to obtain all the names that were repeated two or more times in month six.
Then, in variable2, get all the values that appear in month 7, regardless of the number of times they appear.
The next step is, using INTERCEPT, to obtain the number of users that appear in variable2 that have appeared in variable1.
Obviously I have to create more rows to do this, but first I want for the first step to work out, which is the one that is not working for me 😑
Solved! Go to Solution.
Hi @CLQ ,
You can try this method:
Result =
VAR _table =
FILTER (
'Table',
AND ( 'Table'[Month] = 6, 'Table'[Store] = "TV" || 'Table'[Store] = "Xbox" )
)
VAR _table1 =
GROUPBY ( _table, [Name], "num", COUNTX ( CURRENTGROUP (), [Name] ) )
RETURN
SUMMARIZE ( FILTER ( _table1, [num] > 1 ), [Name] )
The result is:
Hope this helps you.
Here is my PBIX file.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @CLQ ,
You can try this method:
Result =
VAR _table =
FILTER (
'Table',
AND ( 'Table'[Month] = 6, 'Table'[Store] = "TV" || 'Table'[Store] = "Xbox" )
)
VAR _table1 =
GROUPBY ( _table, [Name], "num", COUNTX ( CURRENTGROUP (), [Name] ) )
RETURN
SUMMARIZE ( FILTER ( _table1, [num] > 1 ), [Name] )
The result is:
Hope this helps you.
Here is my PBIX file.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@CLQ Try:
Table =
VAR __Table = GROUBY(FILTER('Table',[Store] <> "Phone" && [Month] = 6),[Name],"__Count",COUNTX(CURRENTGROUP(),[Order Date]))
VAR __Result = FILTER(__Table,[__Count] > 1)
RETURN
__Result
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |