Matrix: How to show only rows with numbers in all 4 columns.

[croberts21]

I have a matrix which is summing EstHours, ActHours, EstCost, and Act cost, by job, and does some other calculations. But only jobs which have EstHours, ActHours, EstCost, and ActCost should be shown in the matrix. The results below are incorrect. 

I only want to show jobs 100001 and 100003 in the report, and jobs which have numbers (non-zero) in those 4 columns above. This would be the program code if this was a program:

if (EstHours<>0) and (ActHours<>0) and (EstCost<>0) and (ActCost<>0) then

show the job

end if

The columns "Est hrs less Act hrs" and "Est less act cost" and "Pct of est cost" are all measures. 

I have been watching a bunch of videos and tutorials and did several searches but I have not see anything similar to this. 

Can anyone provide me some direction? 

This is a link to a zip file with the PBIX file and supporting XLSX file with fake sample data. https://gilsongraphics-my.sharepoint.com/:u:/p/croberts/ERyJqw3HDl9KtTw38CURptIBlEOgCnXy9rg1RzOSJJvB...

Thank you.

[PaulDBrown · Accepted solution]

Create a new measure as follows:

Filter =IF (NOT(ISBLANK([EstHour])) && NOT(ISBLANK([ActHours])) && NOT(ISBLANK([EstCost])) && NOT(ISBLANK([ActCost])), 1)

Add the measure to the filter pane for the visual and set the value to 1

[croberts21]

I'm working with a matrix and I got an error "The value for 'EstHours' cannot be determined. Either the column doesn't exist or there is no current row for this column.' I think I need to use aggregate functions in this measure. 

EDIT:

Yep! This worked. 

ShowJobFlag2 = If (not(isblank(SUM(Jobcost[EstHours])))

&& not(isblank(SUM(Jobcost[ActHours])))

&& not(isblank(SUM(Jobcost[EstCost])))

&& not(isblank(SUM(Jobcost[ActCost])))

,1)

But I am unable to add it to the page filter. I made another question for that. https://community.powerbi.com/t5/Desktop/PBI-Dec-2021-Cannot-add-measure-to-a-filter/m-p/2452859#M87...

[PaulDBrown]

You cannot use measures as filters in the page filters. You can however apply the filter to many/all visuals in the page

[serpiva64]

Hi,

you can create a measure in which you multiply the values of your 4 columns, something like this:

Measure = sumx('Table','Table'[Column2]*'Table'[Column3]*'Table'[Column4])

and then you add it in your filters M

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[croberts21]

I made a measure called ShowJobFlag. But ShowJobFlag always returns blank. It seems that if any single job has one blank field, then ShowJobFlag returns blank. And every job will have one blank field in all the records for that job. My spreadsheet source data looks like this:

My results look like this in the matrix and you can see the measure formula:

The actual and estimate costs and hours are spread among many individual records for a given job. So the function I need should look only at the aggregate sums for each job in the matrix, i.e. the aggregate sums for EstCost, ActCost, EstHours, ActHours. 

My sample data is all in the zip file in my first post. 

EDIT: Hmm. The DAX reference says 

• Only the numbers in the column are counted. Blanks, logical values, and text are ignored.

So I'm not sure what is really going on here. Reference for SUMX is here: 

https://docs.microsoft.com/en-us/dax/sumx-function-dax

[croberts21]

What a great idea!. If any one value is zero, the result is zero. Will this work if the value for one of those columns is blank or null?

[serpiva64]

i'm not able to access your zip file.

But you can try like this

changing * with + 

Measure = sumx('Table','Table'[Column2]+'Table'[Column3]+'Table'[Column4])

[serpiva64]

hi,

it surely functions with null (i tried) and i think also with blank.

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !