Multiple Queries

[stuieb]

Hi

Could someone please help.  I'm new to PowerBI.  I'm using to reporting with Excel, SQL, (and C# if some complicated stuff was ever needed).

I'm bringing in an Opportunities table from Salesforce (using the PowerBI Salesforce connector.  Amongst other fields, this table contains the date a deal was first called (deposit taken) and the date the deal fell over (crashed).

I'm trying to provide a report that shows the number of deposits taken and deals that crashed for any given month.  I'd usually do this in SQL by creating two queries, one that counts opportunies grouped by deposit date month/year, and another that counts opportunies grouped by the crashed date month/year.  I'd then join these queries by the deposit month/year.  That's simple enough.  I cannot work out how to achieve the same result from within PowerBI.

I thought this report would be good for my foray into PowerBI, a few simple charts were easy enough to do, but this has me sooo frustrated, it seems like a basic requirement, I'm sure I'm missing a concept here.

Could someone please offer some advice?

Thanks

[amitchandak]

I did not get it completely. Is it a case of the common time dimension. Means data of the crash month run by crash date and Deposit Month by deposit date and the month year group together as text.

For such a case, you can join a table with date dimension with multiple dates. One will active at time. Follow link how to create and blog on HR to see how both hire and terminations are handled

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Use of dates

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

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/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
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

[stuieb]

Thanks for replying.

The HR hire/fire is a correct analogy.  I'll look closer, at the moment it's not clear where those calculations need to reside and why one relationship is active and the other inactive.

This is very different from SQL... it's frustrating.

[amitchandak]

DAX works in a different way. Two tables can have only one active relation. More than one relation is like an alias. You need to choose join means alias in the formula.