cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tom_G Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Community Support Team
Community Support Team

Re: IF Statement with Filter

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 other members find it more quickly.

View solution in original post

6 REPLIES 6
Super User
Super User

Re: IF Statement with Filter

@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

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





Tom_G Frequent Visitor
Frequent Visitor

Re: IF Statement with Filter

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!

 
Highlighted
Community Support Team
Community Support Team

Re: IF Statement with Filter

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 other members find it more quickly.
Tom_G Frequent Visitor
Frequent Visitor

Re: IF Statement with Filter

@v-frfei-msft v-frfei-msft

 

 

Community Support Team
Community Support Team

Re: IF Statement with Filter

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 other members find it more quickly.

View solution in original post

Tom_G Frequent Visitor
Frequent Visitor

Re: IF Statement with Filter

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 388 members 3,404 guests
Please welcome our newest community members: