Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am a very new Power BI user. I am trying to get the stacked bar below to show the percent compliant/noncompliant for the various different sites, but it is giving me the total for all sites. I am using the COUNTA function. I thought it might be an issue with how my model is set up. Essentially, I want COUNTA to group by the site name and compliant/noncompliant. The highlighted model below shows where the tables are joined. Any thoughts? Thanks!
Solved! Go to Solution.
HI @Anonymous ,
It seems like your tables have linked with chain relationship mapping with 'cross' filter direction, I haven't found any issues on relationship design. (it may be related to table records mapping)
Maybe you can need to create a summary table to summary these table records to one table based on relationship keys or write measures to manually lookup records from different tables and summary them.
Measure formula:
Measure = VAR currSiteID = VALUES ( Sites[SiteID] ) VAR _monitoringList = CALCULATETABLE ( VALUES ( MonitoringReview[MonitoringID] ), FILTER ( ALLSELECTED ( MonitoringReview ), [SiteID] IN currSiteID ) ) VAR _itemList = CALCULATETABLE ( VALUES ( MonitoringItem[ItemID] ), FILTER ( ALLSELECTED ( MonitoringItem ), [MonitoringID] IN _monitoringList ) ) VAR _resultList = CALCULATETABLE ( VALUES ( MonitoringReviewItem[ResultID] ), FILTER ( ALLSELECTED ( MonitoringReviewItem ), [ItemID] IN _itemList ) ) RETURN CALCULATE ( COUNTA ( MonitoringReviewType[ResultID] ), FILTER ( ALLSELECTED ( MonitoringReviewType ), [ResultID] IN _resultList ), VALUES ( MonitoringReviewType[NonCompliant] ) )
Notice: use Sites[SiteID] as axis, MonitoringReviewType[NonCompliant] as legend, measure as value to create chart.
Regards,
Xiaoxin Sheng
HI @Anonymous ,
It seems like your tables have linked with chain relationship mapping with 'cross' filter direction, I haven't found any issues on relationship design. (it may be related to table records mapping)
Maybe you can need to create a summary table to summary these table records to one table based on relationship keys or write measures to manually lookup records from different tables and summary them.
Measure formula:
Measure = VAR currSiteID = VALUES ( Sites[SiteID] ) VAR _monitoringList = CALCULATETABLE ( VALUES ( MonitoringReview[MonitoringID] ), FILTER ( ALLSELECTED ( MonitoringReview ), [SiteID] IN currSiteID ) ) VAR _itemList = CALCULATETABLE ( VALUES ( MonitoringItem[ItemID] ), FILTER ( ALLSELECTED ( MonitoringItem ), [MonitoringID] IN _monitoringList ) ) VAR _resultList = CALCULATETABLE ( VALUES ( MonitoringReviewItem[ResultID] ), FILTER ( ALLSELECTED ( MonitoringReviewItem ), [ItemID] IN _itemList ) ) RETURN CALCULATE ( COUNTA ( MonitoringReviewType[ResultID] ), FILTER ( ALLSELECTED ( MonitoringReviewType ), [ResultID] IN _resultList ), VALUES ( MonitoringReviewType[NonCompliant] ) )
Notice: use Sites[SiteID] as axis, MonitoringReviewType[NonCompliant] as legend, measure as value to create chart.
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |