Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table with the delivery record, and I would like to generate a table calculated with the one shown, the logic is that if the same ID has "Regular Air" and "Delivery Truck" I only filter the records of "Delivery Truck" However, for the records that only "Delivery Truck" has, they must be kept.
Thanks
https://1drv.ms/u/s!AoIRa67hIHkpgnhvrERbmQ1BBF_Q
Solved! Go to Solution.
@ybatistamayo I was able to fix this with the sample data provided, minor edit.
Table24a =
VAR __Table =
ADDCOLUMNS(
SUMMARIZE('Table24',[ID]),
"HasAir",COUNTROWS(FILTER('Table24',[ID] = EARLIER([ID]) && [Delivery Truck]="Regular Air")),
"HasTruck",COUNTROWS(FILTER('Table24',[ID] = EARLIER([ID]) && [Delivery Truck]="Delivery Truck"))
)
VAR __BadIDs = SELECTCOLUMNS(FILTER(__Table,[HasAir]>=1 && [HasTruck]>=1),"__ID",[ID])
VAR __GoodTable = FILTER('Table24',[ID] IN __BadIDs && [Delivery Truck]="Regular Air")
VAR __OtherGoodTable = FILTER('Table24',NOT([ID] IN __BadIDs))
RETURN
UNION(__GoodTable, __OtherGoodTable)
PBIX is attached below sig. You want Table24 and Table24a.
@ybatistamayo Try:
Table =
VAR __Table =
ADDCOLUMNS(
SUMMARIZE('Table',[ID]),
"HasAir",COUNTROWS(FILTER('Table',[Deliver Truck]="Regular Air")),
"HasTruck",COUNTROWS(FILTER('Table',[Deliver Truck]="Delivery Truck"))
)
VAR __BadIDs = SELECTCOLUMNS(FILTER(__Table,[HasAir]>=1 && [HasTruck]>=1),"__ID",[ID])
VAR __GoodTable = FILTER('Table',[ID] IN __BadIDs && [Deliver Truck]="Delivery Truck")
VAR __OtherGoodTable = FILTER('Table',NOT([ID] IN __BadIDs))
RETURN
UNION(__GoodTable, __OtherGoodTable)
Hi Greg, it doesn't work
@ybatistamayo Actually, I'm surprised it was even syntactically correct quite honestly, I wrote it off the cuff. If you can post your sample data as text so I can copy and paste I will take a look to see if I can correct it. @ me
Oh wait, I think I had something reversed, try:
Table =
VAR __Table =
ADDCOLUMNS(
SUMMARIZE('Table',[ID]),
"HasAir",COUNTROWS(FILTER('Table',[Deliver Truck]="Regular Air")),
"HasTruck",COUNTROWS(FILTER('Table',[Deliver Truck]="Delivery Truck"))
)
VAR __BadIDs = SELECTCOLUMNS(FILTER(__Table,[HasAir]>=1 && [HasTruck]>=1),"__ID",[ID])
VAR __GoodTable = FILTER('Table',[ID] IN __BadIDs && [Deliver Truck]="Regular Air")
VAR __OtherGoodTable = FILTER('Table',NOT([ID] IN __BadIDs))
RETURN
UNION(__GoodTable, __OtherGoodTable)
Hello this is the original data
ID Date Delivery Truck
25 2/21/2017 Regular Air
25 8/9/2016 Regular Air
52 3/20/2017 Regular Air
53 3/20/2017 Regular Air
62 9/21/2016 Delivery Truck
62 9/19/2016 Regular Air
64 12/19/2016 Delivery Truck
64 1/26/2018 Regular Air
67 1/26/2018 Regular Air
68 12/18/2017 Regular Air
78 12/28/2016 Regular Air
87 9/21/2018 Delivery Truck
87 9/21/2018 Regular Air
89 9/22/2018 Regular Air
95 10/22/2017 Delivery Truck
96 10/22/2017 Delivery Truck
97 6/17/2018 Delivery Truck
the expected result is a table like this
Hi,
Why is ID 62 not available in your final table? Please also explain the logic again.
You are right the ID 62 must be available
The logic is that when there are two delivery methods for the same ID, only the "Delivery Air" is maintained, that is, I am only interested in the "Delivery air for orders that have been delivered by more than one route."
however the orders that have only been delivered via "Delivery Truck" then if I keep them
this is the expected result:
Hi,
Has your question already been answered by Greg? Do you need any further help?
Hi Ashish_Mathur the question has been answered, thank you for your contribution as well.
How about, if I wanted to use this table calculated as part of a measure, that counts the ID? That is to say, my final goal is to count ID, from the table that Greg_Deckler generated for me.
@ybatistamayo I was able to fix this with the sample data provided, minor edit.
Table24a =
VAR __Table =
ADDCOLUMNS(
SUMMARIZE('Table24',[ID]),
"HasAir",COUNTROWS(FILTER('Table24',[ID] = EARLIER([ID]) && [Delivery Truck]="Regular Air")),
"HasTruck",COUNTROWS(FILTER('Table24',[ID] = EARLIER([ID]) && [Delivery Truck]="Delivery Truck"))
)
VAR __BadIDs = SELECTCOLUMNS(FILTER(__Table,[HasAir]>=1 && [HasTruck]>=1),"__ID",[ID])
VAR __GoodTable = FILTER('Table24',[ID] IN __BadIDs && [Delivery Truck]="Regular Air")
VAR __OtherGoodTable = FILTER('Table24',NOT([ID] IN __BadIDs))
RETURN
UNION(__GoodTable, __OtherGoodTable)
PBIX is attached below sig. You want Table24 and Table24a.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |