Forum Discussion
Need DAX script to count, using several criteria
I need the DAX script that will provide the results in the column "# of J results for chemical 'a' equal to or above guideline" in the table below titled 'Desired Output'. 'Desired Output' is produced from the data in tables 'Results' and 'Guidelines'. 'Results' is a table of chemical concentration results. The column 'result type' is the type of sample from which the concentrations are measured. 'Guidelines' is a table of health guidelines for the various chemicals. Concentrations should be below the guidelines for public safety. 'Desired Output' counts the number of results at each factory that meet all the following criteria: (i) for chemical 'a' (ii) for result type J (iii) equal to or greater than chemical 'a' guideline. Thanks in advance.
10 Replies
I think I follow what you are saying. Can you post data in text so I don't have to type it all in to test things out? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
This will likely have syntax errors because I don't want to type the data in to test:
Measure = VAR __Factory = MAX('Table'[factory]) VAR __Chemical = MAX('Table'[chemical name]) VAR __ResultType = MAX('Table'[result type]) VAR __Table = ADDCOLUMNS( FILTER('Table',[factory] = __Factory && [chemical name] = __Chemical && [result type] = __ResultType), "__Guidline",LOOKUPVALUE('Table2'[guideline],'Table2'[chemical name],__Chemical) ) RETURN COUNTROWS(FILTER(__Table,[result] >= [__Guideline]))Maybe...
Hi Greg, I'd like to attach the Excel file that the image is from. How do I do that? The attachment options here seem to include only photos and media. Thanks
Yeah, you will need to use an outside file sharing service like OneDrive, Box, etc.
Hi,
Here's one simple way:
- Create a relationship from the Chemcial Name column of the Results Table to the Chemical Name column of the Guidelines table
- In the Results table, write a calculated column formula to bring over the values from the guideline table: Guideline values = RELATED(Guidelines[Guideline])
- In the Results table, write another calculated column formula to calculate the difference: Difference = [Result]-[Guideline values]
- To your Table visual, drag Factory from the Results Table. Create slicers for Chemcial Name and Result type from the Results table. In the slicers, select a and j
- Write this measure and drag it to your visual
=CALCULATE(COUNTROWS(Results),Results[Difference]>0)
Hope this helps.
Can you provide data in table format. In between try
Measure = var _aval = maxx(filter(Guidelines,name ="a"),Guidelines[guideline]) return calculate(max(results[result]),filter(results,[Chemical Type]="a" && results[result type] ="j" && results[result] >_aval)). use with factory of result in a table or matrix
How do I provide data in table format? Power BI doesn't allow me to copy and paste from Excel files into these forum posts. The table insert icon doesn't seem to work either.
Is your proposed solution counting the instances at each factory or is it determining the max value at each factory?
BTW, I didn't mention it earlier, but in my data model the tables 'Results' and 'Guidelines' are linked on the field 'chemical name'
