cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bhmiller89
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

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

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.

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. 

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.

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.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!