cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

DAX: FILTER issue with scalar values

I have a list of job numbers and with those job numbers is a list of files uploaded. There can be multiple files uploaded under the same job number.  I want to look at just the job numbers and instances of job numbers that have the tag "AGREEMENT." 

 

Initially wrote "IF('dmg vwJMFilesLib'[Tag]= "AGREEMENT", "Yes, "No") but that didn't work because it was not only taking into account the one instance of the job number that did have an agreement. 

 

Per this picture, 2009-PJJ-J01 has two different files uploaded.  One is tagged "Agreement" and the other is "Null." I need to write something saying "if 2009-PJJ-J01 (Job) has at least 1 "Agreement" Tag, don't show the the Job Number. I'm only interested in any job lacking an "Agreement" Tagged file. 

 

Capture.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

I posted this earlier, but some issue with forum.  Anyway, it works for me.  https://dl.dropboxusercontent.com/u/30711565/missing%20agreement.pbix  

 

Unless I misunderstand



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

4 REPLIES 4
Highlighted

how about this.

 

Jobs missing agreements = if(calculate(countrows(vwJMFilesLib),vwJMFilesLib[Tag]="Agreement")=0,1)

 

Put this measure in a table or matrix, and the job number on rows



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted

No, didn't work.

 

Basically need to find a way to say "If Job Number has more than one occurence, exclude all occurences except where the "Tag" column = "Agreement." 

 

That way I can get a list of Job NUmbers that don't have agreement file types.  Most Job Numbers have multiple file associations, but I need to single out jobs that do not have "Agreements" at all. 

Highlighted

I may not be understanding what you want, but the formula I gave you works as I expect it to and as I understand the problem.    It clearly shows the job numbers that don't have an agreement.  Here is a sample

 

https://dl.dropboxusercontent.com/u/30711565/missing%20agreement.pbix 

 

If it is not what you are after because I don't correctly understand, then that is a different issue of course.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted

I posted this earlier, but some issue with forum.  Anyway, it works for me.  https://dl.dropboxusercontent.com/u/30711565/missing%20agreement.pbix  

 

Unless I misunderstand



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors