Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Compare 2 string columns in Analysis services using DAX

Hello world, 
so I have been using analysis services in power bi and I want to compare 2 string columns. 
The 2 string columns come from different DIM tables of the model, that are being combined through a FACT table. 
I want to compare those two columns somehow, but unfortunately , no matter what I've tried didn't work. 
Initially I used this:

 
Table 2 =
var combinetable = ADDCOLUMNS('Fact - Purchase Orders',"Requestor",RELATED(
'Dim - Purchase Requisitions'[Requisition Preparer]
),"Preparer",RELATED('RELATED('Dim - Purchase Orders'[PO Header Created By]
))
return
 
in order to combine those two columns in a table and the somehow with an if make the comparison, but unforunately it doesn't work.
Any ideas?
I have no access to change the model (as said, analysis services) so I can only work with DAX. 
The three tables are
Dim - Purchase Orders
Dim - Purchase Requisitions
Fact - Purchase Orders
The point is to see if the Requisition Preparer (from Dim - Purchase Requisition) is the same with the PO Header Created by (from Dim Purchase Orders).

I know that there's a need for a pbix file but as said, it is analysis services.
Thank
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    If you have columns like [ID] to create relationships between two tables, you can try to create a measure like this and set the visual filter:

    Check = 
IF (
    CONTAINS (
        VALUES ( 'Table11'[Requester] ),
        'Table11'[Requester], SELECTEDVALUE ( Table22[PO Creator] )
    ),
    "OK",
    "Not OK"
)

    Table visual would not show the duplicated row if not put separate column in it:

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try like

    Table 2 =
    var combinetable = ADDCOLUMNS('Fact - Purchase Orders',"Requestor",RELATED(
    'Dim - Purchase Requisitions'[Requisition Preparer]
    ),"Preparer",RELATED('Dim - Purchase Orders'[PO Header Created By]
    ))
    return

     

    Can you share sample data and sample output in table format?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Requester     PO Creator    Output

      CGC               CGC                1

      CGC               KLP                 0

      OTI                OTI                  1

      OTI                VLS                  0

      CGC              CGC                 1
      VLS               VLS                  1
      KLP                                       0

       

      This is some sample data, bear in mind the constraints mentioned on the initial message. 
      Thanks amitchandak 

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        Anonymous , Try a measure like where 'Fact - Purchase Orders'[ID] is there level where you want to compare these names, replace with the correct name 

         

        New measure = countx(values('Fact - Purchase Orders'[ID]),If( RELATED('Dim - Purchase Requisitions'[Requisition Preparer]) =RELATED('Dim - Purchase Orders'[PO Header Created By]) ,1,0))

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    Hi Anonymous ,

    If you have columns like [ID] to create relationships between two tables, you can try to create a measure like this and set the visual filter:

    Check = 
IF (
    CONTAINS (
        VALUES ( 'Table11'[Requester] ),
        'Table11'[Requester], SELECTEDVALUE ( Table22[PO Creator] )
    ),
    "OK",
    "Not OK"
)

    Table visual would not show the duplicated row if not put separate column in it:

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.