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...
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"
    )
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.