Metrics column breaks filter in visual

[imocean]

Hi there,

I've got a quite simple data model where there is a Distributors table, and a Drivers table with lookup to distributor, and a ComplianceRecords table containing all the compliance each driver needs to meet and the current status (Yes or No). So I created a measure to show the compliance rate i.e. 

%Compliance = If(COUNTROWS(Filter(compliancerecord,compliancerecord[compliancestatus]="Yes"))=0,0,COUNTROWS(Filter(compliancerecord,compliancerecord[compliancestatus]="Yes")) / CALCULATE(COUNT(compliancerecord[compliancerecordid]), ALLSELECTED(compliancerecord[compliancestatus])))

However when I try to filter on some attibute say State (in Distributor table), if I add %Compliance measure in the visual the rows are not filtered to show only Drivers relevant to the selected State (the table visual on left shows correct filtered rows, while the right doesn't).  How can I make the filtering working with the metrics meassure column? 

Could be an easy question to many! 

Thanks a lot

[imocean · Accepted solution]

Got it working - I created custom column on Drivers table to calculate each Driver's metrics. By doing that it filters off those not relevant to the filters. Not sure if it's the best way though. 

[imocean]

Thanks very much @jdbuchanan71 . I tried separating to 3 measures but it does the same. The main challenge is that I want to display 0% as well, so with your method I changed on the Compliant % to be

Compliant % = IF( [Compliant Count] = 0, 0, DIVIDE ( [Compliant Count], [All Selected Count] ))

However adding the 0% made all the rows visible in the visual. 

[jdbuchanan71]

@imocean 

Can you try it with 3 measures?

All Selected Count = CALCULATE ( COUNTROWS ( compliancerecord ), ALLSELECTED ( compliancerecord[Status] ) )

Compliant Count = COUNTROWS ( FILTER ( compliancerecord, compliancerecord[status] = "Yes" ) )

Compliant % = DIVIDE ( [Compliant Count], [All Selected Count] )

That way you also have the count measures available for use in visuals.