Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
stuieb
Frequent Visitor

Two Queries on Report

Hi

 

I'm coming here, as I'm losing my mind with PowerBI.

 

I've come from a background, transforming datasets with SQL, C# and Excel.

 

What seemed like an elementary report to  produce as more foray into PowerBI is driving me insane and is frustrating me.

 

Could somone lend some advice, I feel like I'm missing a concept here that's making this more difficult than it should be.

 

For this report I have a table of Salesforce opportunities, amongst other fields, where have a date that a deal was called and another field containing the date that the deal crashed or fell through.  I'd like to chart the count of deals that were made in a month along with the number of deals that crashed in that same month.  Previously I would have written two queries in SQL, one that counted deals and grouped by the month the deal was made and another counting the deals but grouping by the month they crashed, I'd then join these two queries by that month.  I can't do this in SQL as it's importing from Salesforce directly to PowerBI.

 

Could some provide some advice or suggest a resource that might help?

 

Thanks

1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

Hi @stuieb ,

 

You may create a calendar table using DAX below, then create relationship between the two tables on date field, assuming it is the [deal made date] for your fact table.

 

Calendar=CALENDARAUTO()

 

Then you may create measures like DAX below.

 

Count for made date=CALCULATE(COUNT(Table1[Deal]), FILTER(ALLSELECTED(Table1), MONTH(Table1[deal made date])= MONTH(MAX(Table1[deal made date]))))
 
Count for crashed date=CALCULATE(COUNT(Table1[Deal]), FILTER(ALLSELECTED(Table1), MONTH(Table1[deal crashed date])= MONTH(MAX(Table1[deal crashed date]))), USERELATIONSHIP(Table1[deal crashed date], Calendar[Date])

 

You may choose Clustered column chart to display the data, put Calendar[Date] into Axis box, and put the two new created measures above into Value box.

 

If I misunderstood it, could you please share your sample data or desired output screenshots for further analysis, you can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

 

Best Regards,

Amy

 

Community Support Team _ Amy

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

View solution in original post

1 REPLY 1
v-xicai
Community Support
Community Support

Hi @stuieb ,

 

You may create a calendar table using DAX below, then create relationship between the two tables on date field, assuming it is the [deal made date] for your fact table.

 

Calendar=CALENDARAUTO()

 

Then you may create measures like DAX below.

 

Count for made date=CALCULATE(COUNT(Table1[Deal]), FILTER(ALLSELECTED(Table1), MONTH(Table1[deal made date])= MONTH(MAX(Table1[deal made date]))))
 
Count for crashed date=CALCULATE(COUNT(Table1[Deal]), FILTER(ALLSELECTED(Table1), MONTH(Table1[deal crashed date])= MONTH(MAX(Table1[deal crashed date]))), USERELATIONSHIP(Table1[deal crashed date], Calendar[Date])

 

You may choose Clustered column chart to display the data, put Calendar[Date] into Axis box, and put the two new created measures above into Value box.

 

If I misunderstood it, could you please share your sample data or desired output screenshots for further analysis, you can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

 

Best Regards,

Amy

 

Community Support Team _ Amy

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors