Forum Discussion

camarillob's avatar
camarillob
Frequent Visitor
1 year ago
Solved

Categorizing 3 Columns into a Category

Hi all,

 

Please help me on how I could create a relationship on these two tables.

 

 

Some key points for this

  • For Table B, all blank values are null.
  • For Table B, some categories are duplicated, but ALL values (from CLASS1-3) are unique.
  • Im looking for a way so that when I present a matrix and use values from Table A, it will output the category from Table B without issues.

Sample Output:

Need Help
  • elitesmitpatel's avatar
    elitesmitpatel
    1 year ago

    Hey camarillob 
    here is the solution 
    create a calculated column in both the table 

    Unique id =
     var a = IF(LEN(Sheet2[CLASS1])> 1 ,"", Sheet2[CLASS1])
    var b = IF(LEN(Sheet2[CLASS2])> 1 ,"", Sheet2[CLASS2])
     var c = IF(LEN(Sheet2[CLASS3])> 1 ,"", Sheet2[CLASS3])
     RETURN
     CONCATENATE(a,CONCATENATE(b,c))

    Unique id =
     var a = IF(LEN(Sheet1[CLASS1])> 1 ,"", Sheet1[CLASS1])
    var b = IF(LEN(Sheet1[CLASS2])> 1 ,"", Sheet1[CLASS2])
     var c = IF(LEN(Sheet1[CLASS3])> 1 ,"", Sheet1[CLASS3])
     RETURN
     CONCATENATE(a,CONCATENATE(b,c))

    then make the realtionship and the drag the column in table.


    Here is the file 
    Solved PBIX 

    if it helps please Kudos to work and Accept it as Solution

1 Reply

  • elitesmitpatel's avatar
    elitesmitpatel
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

    Hey camarillob 
    here is the solution 
    create a calculated column in both the table 

    Unique id =
     var a = IF(LEN(Sheet2[CLASS1])> 1 ,"", Sheet2[CLASS1])
    var b = IF(LEN(Sheet2[CLASS2])> 1 ,"", Sheet2[CLASS2])
     var c = IF(LEN(Sheet2[CLASS3])> 1 ,"", Sheet2[CLASS3])
     RETURN
     CONCATENATE(a,CONCATENATE(b,c))

    Unique id =
     var a = IF(LEN(Sheet1[CLASS1])> 1 ,"", Sheet1[CLASS1])
    var b = IF(LEN(Sheet1[CLASS2])> 1 ,"", Sheet1[CLASS2])
     var c = IF(LEN(Sheet1[CLASS3])> 1 ,"", Sheet1[CLASS3])
     RETURN
     CONCATENATE(a,CONCATENATE(b,c))

    then make the realtionship and the drag the column in table.


    Here is the file 
    Solved PBIX 

    if it helps please Kudos to work and Accept it as Solution