Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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" )
Pbix as attached.
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" ) )
Hi @v-frfei-msft v-frfei-msft
Thanks for getting back to me. I tried the formula provided and I think it's nearly there but bidirectional should only be written when one plant has LoadingTruck AND ReceivingTruck. For example Plant C only has LoadingTruck so it should be Onedirection. Any thoughts on how to do this?
Thanks
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.
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
@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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |