Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ybatistamayo
Helper III
Helper III

table calculated by a single filter argument

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 

 

result.png

1 ACCEPTED 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.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

@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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi Greg, it doesn't work

ybatistamayo_0-1632254000466.png

 

@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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

result.png

Hi,

Why is ID 62 not available in your final table?  Please also explain the logic again.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:

result.new.png

Hi,

Has your question already been answered by Greg?  Do you need any further help?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.