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
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 an 8 times 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 an 8 times 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 an 8 times 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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.