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.

     

Tom_G's avatar
Tom_G
Helper II
7 years ago

Hi - thanks for the link. Let me try to explain this again. I have one table of data - let's call it Table1 as below with Plant Names and Access Type columns - in reality there are many more columns that this.

 

What I want to do is have a calculated column as below which evaluates whether for each plant it has the ability to load AND receive by one or more types of transport. If so, the word "Bidirectional" would be written in the calculated column. Plant A can Load AND Receive by truck so that would be bidirectional whereas Plant B can only receive by ship and load railway by railway so it should not have bidirectional written in the calculated column.

 

Plant NameAccess TypesCalculated Column
ALoadingTruckBidirectional
AReceivingTruckBidirectional
AReceivingShip 
BReceivingShip 
BLoadingRailway 
CReceivingShipBidirectional
CLoadingShipBidirectional
CLoadingTruck 
DLoadingBargeBidirectional
DReceivingBargeBidirectional
DLoadingShipBidirectional
DReceivingShipBidirectional

 

I tried this formula below to try and get it working for the truck access type first - but the result is a blank column. I was thinking maybe I need some kind of If Statement but I struggle to get something that evaluates for each plant. Maybe there are other ways to get the result.

Bidirectional = IF(HASONEVALUE(Table1[Plant Name]);IF(Table1[Access Types] IN {"LoadingTruck";"ReceivingTruck"};"Bidirectional";"Onedirection"))

Any help much appreciated!

 
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