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.

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi Tom_G ,

 

To create a calculated column as below.

 

Bidirectional = 
IF (
    ISBLANK ( Table1[Plant Name] ),
    BLANK (),
    IF (
        Table1[Access Types] IN { "LoadingTruck", "ReceivingTruck" },
        "Bidirectional",
        "Onedirection"
    )
)

 

Tom_G's avatar
Tom_G
Helper II
7 years ago

v-frfei-msft v-frfei-msft

 

 

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    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.

     

    • Tom_G's avatar
      Tom_G
      Helper II
      7 years ago

      Hi v-frfei-msft 

       

      Many thanks for your help and for the impressive formula - it works! It was actually quite a bit more complicated than I imagined.

      Regards,

      Tom