Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I have 2 tables
table1
ID
1
2
3
4
5
6
7
Table 2
ID code
1 56265001
1 38341003
1 56265001
2 709044004
2 13645005
3 73211009
Table1 - id column is primary key
table2- id column is foreign key
Requirment:-
if table2 count of code >= 2 based on the id column then "categroy1"
if table2 count of code = 1 based on the id column then "category2"
if no any entry present in the table 2 based on the id then "category3"
output:-
Type count of id
Category1 2
category2 1
category3 4
Solved! Go to Solution.
@Anonymous , Try a new column in table 1
new column =
var _cnt =countx(filter(Table2,table1[ID] = table2[ID] ),Table2[Code])+0
return
switch(true(),
_cnt=0, "category3"
,_cnt=1, "category1"
,"category2")
@Anonymous , Try a new column in table 1
new column =
var _cnt =countx(filter(Table2,table1[ID] = table2[ID] ),Table2[Code])+0
return
switch(true(),
_cnt=0, "category3"
,_cnt=1, "category1"
,"category2")
Hi @Anonymous
Could you tell me if your problem has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
Hi @Anonymous , try this
Category =
var __cnt = CALCULATE(COUNTA(Tab2[Code]), FILTER(Tab2, Tab2[ID] = Tab1[ID]))
return
SWITCH(TRUE(),
__cnt >=2, "Category 1", __cnt=1, "Category 2", "Category 3")
Hope this helps
David
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
219 | |
89 | |
76 | |
67 | |
60 |