Forum Discussion

Tom_G's avatar
Tom_G
Helper II
7 years ago
Solved

IF Statement with Filter

Hi - i'm new to DAX. I have table with a column with the name of plants and another column with the types of access to them listed on separate rows - such as LoadingTruck, ReceivingTruck, LoadingShip...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Tom_G ,

     

    To create two calculated column as below.

    type = 
VAR load =
    LEN ( "Loading" )
VAR rece =
    LEN ( "Receiving" )
VAR searl =
    SEARCH ( "Loading", 'Table1'[Access Types], 1, BLANK () )
VAR searr =
    SEARCH ( "Receiving", 'Table1'[Access Types], 1, BLANK () )
RETURN
    IF (
        searl <> BLANK (),
        RIGHT ( 'Table1'[Access Types], LEN ( 'Table1'[Access Types] ) - load ),
        IF (
            searr <> BLANK (),
            RIGHT ( 'Table1'[Access Types], LEN ( 'Table1'[Access Types] ) - rece )
        )
    )

    Column = 
VAR countr =
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            Table1[Plant Name] = EARLIER ( Table1[Plant Name] )
                && Table1[type] = EARLIER ( Table1[type] )
        )
    )
RETURN
    IF ( countr > 1, "Bidirectional", "Onedirection" )

     

    Pbix as attached.