Forum Discussion

kilala's avatar
kilala
Icon for Resolver I rankResolver I
4 years ago
Solved

Categorise by comparing 2 column

Dear all,

 

In my fact table, I have 2 ID columns(column A and column B) that may have 3 kind of relationship:

1. One-to-One: 1 ID in column A is related to 1 ID in column B

2. One-to-Many:  1 ID in column A is related to many ID in column B

3. Many-to-One:  Many ID in column A is related to one ID in column B.

 

I need to create another column ( in this case I need to create Measure as I connected to live PBI dataset) , and my expected result would be:

 

Column AColumn BCategory
A1B11-1
A2B21-M
A2B31-M
A3B4M-1
A4B4M-1

 

Any tips/ help on how can I achieve this objective? Many thanks!

Need Help
Show and Tell
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    If you want to check creating a measure, please check the below picture and the attached pbix file.

     

     

    Category measure: =
VAR currentA =
    MAX ( Data[Column A] )
VAR currentB =
    MAX ( Data[Column B] )
RETURN
    SWITCH (
        TRUE (),
        COUNTROWS ( VALUES ( Data[Column A] ) ) = 1
            && CALCULATE (
                COUNTROWS ( VALUES ( Data[Column B] ) ),
                FILTER ( ALL ( Data ), Data[Column A] = currentA )
            ) = 1
            && CALCULATE (
                COUNTROWS ( data ),
                FILTER ( ALL ( Data ), Data[Column B] = currentB )
            ) = 1, "1-1",
        COUNTROWS ( VALUES ( Data[Column A] ) ) = 1
            && CALCULATE (
                COUNTROWS ( VALUES ( Data[Column B] ) ),
                FILTER ( ALL ( Data ), Data[Column A] = currentA )
            ) > 1, "1-M",
        COUNTROWS ( VALUES ( Data[Column A] ) ) = 1
            && CALCULATE (
                COUNTROWS ( VALUES ( Data[Column B] ) ),
                FILTER ( ALL ( Data ), Data[Column A] = currentA )
            ) = 1
            && CALCULATE (
                COUNTROWS ( data ),
                FILTER ( ALL ( Data ), Data[Column B] = currentB )
            ) > 1, "M-1"
    )

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    kilala , Try to have new column like

     

    New column =
    var _1 = countx(filter(Table, [Column A] = earlier([Column A]) ), [Column B])
    var _2 = countx(filter(Table, [Column B] = earlier([Column B]) ), [Column A])
    return
    Switch(True() ,
    _1 =1 && _2 =1, "1-1" ,
    _1 =1 && _2 >1, "1-M" ,
    _1 >1 && _2 =1, "M-1" ,
    "M-M"
    )

    • kilala's avatar
      kilala
      Icon for Resolver I rankResolver I
      4 years ago

      Hi, tanks for the reply! Unfortunately, I cannot create new column as I connecting to live data. I am only able to create measures. 

       

      However, I tried your approach but an error shows here:

      var _1 = countx(filter(Table, [Column A] = earlier([Column A]) ), [Column B])

       

      error message:

      Parameter is not correct type; cannot find name [Column A]

      please help!

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    If you want to check creating a measure, please check the below picture and the attached pbix file.

     

     

    Category measure: =
VAR currentA =
    MAX ( Data[Column A] )
VAR currentB =
    MAX ( Data[Column B] )
RETURN
    SWITCH (
        TRUE (),
        COUNTROWS ( VALUES ( Data[Column A] ) ) = 1
            && CALCULATE (
                COUNTROWS ( VALUES ( Data[Column B] ) ),
                FILTER ( ALL ( Data ), Data[Column A] = currentA )
            ) = 1
            && CALCULATE (
                COUNTROWS ( data ),
                FILTER ( ALL ( Data ), Data[Column B] = currentB )
            ) = 1, "1-1",
        COUNTROWS ( VALUES ( Data[Column A] ) ) = 1
            && CALCULATE (
                COUNTROWS ( VALUES ( Data[Column B] ) ),
                FILTER ( ALL ( Data ), Data[Column A] = currentA )
            ) > 1, "1-M",
        COUNTROWS ( VALUES ( Data[Column A] ) ) = 1
            && CALCULATE (
                COUNTROWS ( VALUES ( Data[Column B] ) ),
                FILTER ( ALL ( Data ), Data[Column A] = currentA )
            ) = 1
            && CALCULATE (
                COUNTROWS ( data ),
                FILTER ( ALL ( Data ), Data[Column B] = currentB )
            ) > 1, "M-1"
    )
    • kilala's avatar
      kilala
      Icon for Resolver I rankResolver I
      4 years ago

      dear Jihwan_Kim ,

      Thanks a lot! It works perfectly fine.

       

      I wish to add another rules where Column A = "NA", then the category = Blank().

       

      I added this logic as 1st rule:

      CALCULATE (
      FILTER ( ALL ( Data ), Data[ColumnA] = currentA )
      ) = "NA",Blank()

      However, following error occurs, I'm not sure why.