Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
User | Count |
---|---|
128 | |
72 | |
70 | |
58 | |
53 |
User | Count |
---|---|
193 | |
96 | |
66 | |
62 | |
52 |