Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tom_G
Helper II
Helper II

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, ReceivingShip etc. I want to create a calculated column that evaluates for each plant whether it has the ability to load AND receive either by truck, ship, rail, barge etc and if true writes the word "bidirectional" in the calculated column.

 

I can write an IF statement that evaluates whether the words LoadingTruck and ReceivingTruck appear in the column but I can't work out how to link this to the plant name.

 

Any ideas of how to do this would be much appreicated.

 

 

 

 

 

 

1 ACCEPTED SOLUTION

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

2.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

6 REPLIES 6
Tom_G
Helper II
Helper II

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!

 

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

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

@v-frfei-msft v-frfei-msft

 

 

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

2.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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

parry2k
Super User
Super User

@Tom_G read this post to get your answer quickly.

 

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.