Forum Discussion

Ilka719's avatar
Ilka719
Frequent Visitor
2 years ago
Solved

How to compare values in same column?

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 = CALCULAT...
Need Help
  • danextian's avatar
    danextian
    2 years ago

    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 

danextian's avatar
danextian
Super User
2 years ago

Ilka719 

 

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.

 

danextian's avatar
danextian
Super User
2 years ago

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