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

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"
)
Ilka719's avatar
Ilka719
Frequent Visitor
2 years ago

Hi! 🙂
Tried, but the result is endless: 

😞

 

  • 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