Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
How could I compare is there are same value in different groups in same table?
Trying this code:
Same =
var KP = CALCULATE(VALUES(Table[Key]),FILTER(Table,Table[Group]="Apple"))
var KA = CALCULATE(VALUES(Table[Key]),FILTER(Table,Table[Group]="Orange"))
return
if(KP=KA,"same","no")
But detects error: A circular dependency was detected
Solved! Go to Solution.
I would create a summary table that contains the group to lessen the number of rows to be scanned and key then create a calculatead column to count whether the key has more than one distinct group. The summary table can either be created in DAX or M. Please see attached sample.
Summary calc table
SummaryTable =
SUMMARIZE ( 'DataTable', 'DataTable'[Group], 'DataTable'[Key] )
calc column in SummaryTable
same_no =
IF (
CALCULATE (
DISTINCTCOUNT ( SummaryTable[Group] ),
ALLEXCEPT ( SummaryTable, SummaryTable[Key] )
) > 1,
"same",
"no"
)
You can then use this table to relate to the original table or as a reference in LOOKUPVALUE. Please see attached sample pbix. https://1drv.ms/f/s!AqxKG9R1145fiHWd4q_vHpM9NRlt?e=669nxs
Proud to be a Super User!
Hi @Ilka719 ,
If you're trying to determine if a particualr key in apple exists in orange as well, try this:
=
IF (
(
CALCULATE (
DISTINCTCOUNT ( 'table'[key] ),
FILTER (
'table',
'table'[key] = EARLIER ( 'table'[key] )
&& 'table'[group] = "Apple"
)
)
+ CALCULATE (
DISTINCTCOUNT ( 'table'[key] ),
FILTER (
'table',
'table'[key] = EARLIER ( 'table'[key] )
&& 'table'[group] = "Orange"
)
)
) > 1,
"same",
"no"
)
Proud to be a Super User!
Hi! 🙂
Tried, but the result is endless:
😞
I'm assuming you have a very large table. One more fact that should be mentioned when asking for help. Or possibly, there are other calculations that are slowing this down.
Proud to be a Super User!
I would create a summary table that contains the group to lessen the number of rows to be scanned and key then create a calculatead column to count whether the key has more than one distinct group. The summary table can either be created in DAX or M. Please see attached sample.
Summary calc table
SummaryTable =
SUMMARIZE ( 'DataTable', 'DataTable'[Group], 'DataTable'[Key] )
calc column in SummaryTable
same_no =
IF (
CALCULATE (
DISTINCTCOUNT ( SummaryTable[Group] ),
ALLEXCEPT ( SummaryTable, SummaryTable[Key] )
) > 1,
"same",
"no"
)
You can then use this table to relate to the original table or as a reference in LOOKUPVALUE. Please see attached sample pbix. https://1drv.ms/f/s!AqxKG9R1145fiHWd4q_vHpM9NRlt?e=669nxs
Proud to be a Super User!
User | Count |
---|---|
98 | |
89 | |
82 | |
70 | |
67 |
User | Count |
---|---|
114 | |
104 | |
101 | |
72 | |
65 |