Chart that summarizes multiple data sets

[danielb35]

Hi,

I'm new to power BI and looking for guidence on this use case. I want to provide the end user with a dashboard that summarizes the number of controls that have issues, then have the functionality to click into the chart to view the details behind the control.  The issue I'm running into is that each control is a seperate query with different columns and no join key.  Is it possible for Power BI to summarize data in one chart without a join key? 

Data example:

Data Set 1

Control Title| ID| ChildID|StartDate|EffectiveDate|

Control 1     |  1  | 1690| 6/1/2019|5/31/2019

Control 1     |   2 | 2680| 1/1/2015| 12/31/2014

Data Set 2

ControlTitle| MV Diff| CF Diff|EQ Diff|

Control 2   | -1000| 0|.82|.55

Control 2   | 5000 |5  |70 |99

Power BI Visual 

Control Title   Number of Issues

Control 1      2

Control 2     2 

[v-jayw-msft · Accepted solution]

Hi @danielb35 ,

Please check following steps as below.

1# Use Append Queries feature in Query Editor.

2# Create measure and visual.

Number of Issues = COUNTROWS(Append1)

3# Add Drillthrough

Once you right-click a control on the table visual above and click drillthrough, it will show the detail of the control.

Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danielb35]

Thank-you @v-jayw-msft for the guidence.  Your solution has put me on the right path. I appreciate the feedback. 

[amitchandak]

You can use union and summarize

Example

 Balance Sheet = union(SUMMARIZE(Original ,Original[TDCVAL],"Col1",max(Sales[Sales]),"Col2",Max(Original[Amount])*max([Shared Cat ]S Cat1%))
,union(SUMMARIZE(Original ,Original[TDCVAL],"Col1",max(Sales[Sales]),"Col2",Max(Original[Amount])*max([Shared Cat ]S Cat2%))
,union(SUMMARIZE(Original ,Original[TDCVAL],"Col1",max(Sales[Sales]),"Col2",Max(Original[Amount])*max([Shared Cat ]S Cat3%)) )

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601