Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have 2 columns
Source data
ID code
1 56265001
1 38341003
1 56265001
2 709044004
2 13645005
3 73211009
Requirment:-
if count of code greater than 2 based on the id then "category1"
if count of code is 1 based on the id then category2"
output:-
kpi will look like:-
Category1 category2
2 1
Solved! Go to Solution.
Hi @Anonymous ,
You can create this calculated column:
Type =
VAR _count =
CALCULATE ( COUNT ( 'Table 2'[code] ), ALLEXCEPT ( 'Table 1', 'Table 1'[ID] ) )
RETURN
IF (
'Table 1'[ID] IN DISTINCT ( 'Table 2'[ID] ),
IF ( _count >= 2, "Category 1", IF ( _count = 1, "Category 2" ) ),
"Category 3"
)
Attached a sample file that hopes to help you: count rows.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Create a new column in table
category = if(countx(filter(Table,[Source] = earlier([Source] )),[data])>2,"Category 2", "Category 1")
and then use a measure distinctcount(table[Source]) in display
or you need 2 measures
Category 2 = countx(filter(summarize(Table,Table[Source] ,"_1", count(table[data])),[_1] >2),[Source])
Category 1 = countx(filter(summarize(Table,Table[Source] ,"_1", count(table[data])),[_1] <=2),[Source])
I missed the proper requirement @amitchandak
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
category2 4
Hi @Anonymous ,
You can create this calculated column:
Type =
VAR _count =
CALCULATE ( COUNT ( 'Table 2'[code] ), ALLEXCEPT ( 'Table 1', 'Table 1'[ID] ) )
RETURN
IF (
'Table 1'[ID] IN DISTINCT ( 'Table 2'[ID] ),
IF ( _count >= 2, "Category 1", IF ( _count = 1, "Category 2" ) ),
"Category 3"
)
Attached a sample file that hopes to help you: count rows.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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
category2 4
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |