Forum Discussion

Kagliostro's avatar
Kagliostro
Frequent Visitor
2 years ago

How to match comma separated cells values in two different columns and return missing values

Hi Everyone, I would like to use DAX to evaluate names differences between two columns of same table. The result will be displayed in a third column. Therefore given column A - Column B I should get...
Kagliostro's avatar
Kagliostro
Frequent Visitor
2 years ago

The issue I have now is that the drop down menu shows names aggregated rather than being listed singularly. This won't allow me to perform a proper calculation of how many times a nime is cited.

These two names should be listed separately in the table drop-down menu

 

 

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Do just the EXCEPT and feed the result into the slicer.

     

    Note: this needs to be a calculated table.

    • Kagliostro's avatar
      Kagliostro
      Frequent Visitor
      2 years ago

      I think I am missing something....

       

      SME_MANCANTE = 
VAR PathA = SUBSTITUTE( IA_Assigned_to[ColumnA], ",", "|" )
VAR PathB = SUBSTITUTE( IA_Assigned_to[ColumnB], ",", "|" )
VAR TableA = SELECTCOLUMNS(GENERATESERIES( 1, PATHLENGTH(PathA)+1 ),"Value",PATHITEM( PathA, [Value] ))
VAR TableB = SELECTCOLUMNS(GENERATESERIES( 1, PATHLENGTH(PathB)+1  ),"Value",PATHITEM( PathB, [Value] ))
    VAR __Missing = EXCEPT( TableA, TableB )
    VAR __Result = CONCATENATEX( __Missing, [Value], "," )
RETURN  __Result
      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago
        SME_MANCANTE = 
VAR PathA = SUBSTITUTE( IA_Assigned_to[ColumnA], ",", "|" )
VAR PathB = SUBSTITUTE( IA_Assigned_to[ColumnB], ",", "|" )
VAR TableA = SELECTCOLUMNS(GENERATESERIES( 1, PATHLENGTH(PathA)+1 ),"Value",PATHITEM( PathA, [Value] ))
VAR TableB = SELECTCOLUMNS(GENERATESERIES( 1, PATHLENGTH(PathB)+1  ),"Value",PATHITEM( PathB, [Value] ))
RETURN EXCEPT( TableA, TableB )