Forum Discussion

rakesh-k01's avatar
rakesh-k01
Frequent Visitor
9 years ago
Solved

Matrix on the same column

Hi,

 

I have 2 tables having 1 to many relationship with the ref2per table. Capture.PNG

 

I' am trying to get a matrix something like below:

 

nameSAPPCSEventBrite
SAP  1
PCS 1 
EventBrite1 1

SAP and EventBrite has count 1 since person id 1 is shared between them. Same applies on the other way round.  Since person id 2 has only one reference, the value is 1 for matrix EventBrite to EventBrite. 

 

Just using the UI I'am getting the below result.

 

 

 

 

 

 

 

 

 

 I'am still a rookie with the DAX commands, so any thoughts on how the required result can be achieved. 

 

Thanks

Rakesh

 

Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi rakesh-k01,

     

    You can create calculated columns in ref2per table like below:

     

    Name = RELATED('ref_table'[name])

     

    Name(update) = var t=CALCULATE(MAX('ref2per'[ref_source_system_id]),ALLEXCEPT(ref2per,'ref2per'[personal_id]))
    return
    IF('ref2per'[ref_source_system_id]<> t,CALCULATE(MIN('ref2per'[Name]),ALLEXCEPT(ref2per,'ref2per'[personal_id])),
     CALCULATE(MAX('ref2per'[Name]),ALLEXCEPT(ref2per,'ref2per'[personal_id])))

     

     

    Best Regards,
    Qiuyun Yu

2 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    Hi rakesh-k01,

     

    You can create calculated columns in ref2per table like below:

     

    Name = RELATED('ref_table'[name])

     

    Name(update) = var t=CALCULATE(MAX('ref2per'[ref_source_system_id]),ALLEXCEPT(ref2per,'ref2per'[personal_id]))
    return
    IF('ref2per'[ref_source_system_id]<> t,CALCULATE(MIN('ref2per'[Name]),ALLEXCEPT(ref2per,'ref2per'[personal_id])),
     CALCULATE(MAX('ref2per'[Name]),ALLEXCEPT(ref2per,'ref2per'[personal_id])))

     

     

    Best Regards,
    Qiuyun Yu