Forum Discussion
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.
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.
6 Replies
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
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 Name Access Types Calculated Column A LoadingTruck Bidirectional A ReceivingTruck Bidirectional A ReceivingShip B ReceivingShip B LoadingRailway C ReceivingShip Bidirectional C LoadingShip Bidirectional C LoadingTruck D LoadingBarge Bidirectional D ReceivingBarge Bidirectional D LoadingShip Bidirectional D ReceivingShip Bidirectional 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" ) )
