Forum Discussion
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...
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.
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.
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