Forum Discussion

imocean's avatar
imocean
Frequent Visitor
5 years ago
Solved

Metrics column breaks filter in visual

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

 

 

Need Help
  • imocean's avatar
    imocean
    5 years ago

    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. 

3 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    5 years ago

    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.

     

  • imocean's avatar
    imocean
    Frequent Visitor
    5 years ago

    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. 

  • imocean's avatar
    imocean
    Frequent Visitor
    5 years ago

    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.