cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ccsrtw Regular Visitor
Regular Visitor

creating a measure using to data tables and one calendar table

I have two tables - one hosts closed deals (Closed) and the other hosts demos ran (Demos). Both tables have their own date column and their own rep column.

 

I'm leveraging two lookup tables, a calendar table (BasicCalendarUS) and a rep index table (RepIndex). I successfully created three active relationship - Closed to BasicCalendarUS, Closed to RepIndex, and Demos to RepIndex. Trying to link Demos to BasicCalendarUS results in an inactive single direction relationship.

 

So that's the current setup. My goal is to create a Close Rate measure - Deals with Demos (from the Closed table) over Total Demos (from the Demos table).  have the general gist of the formula down - DIVIDE(Deals with Demos, Total Demos) - but when I try use a timeline (MonthName) as my x-axis, the close rate does not change as I want it to. The Deals with Demos piece of the formula alters correctly, but the Total Demos does not budge from the total sum.

 

I'm guessing this has something to do with the BasicCalendarUS relationships - specifically that I cannot have two active relationships, one from Closed and one from Demos.

 

I hope I did a decent job of explaining the issue, but please let me know if I can clarify further!

1 ACCEPTED SOLUTION

Accepted Solutions
richbenmintz
Advisor

Re: creating a measure using to data tables and one calendar table

the issue was your relationships cros filter behhaviour settings, they were all set to Both. a given that repIndex was joined to both closed and demo tables the basiccalennderUS table would filter Demos through Closed and RepIndex. By Updating the relationships to be single direction you are able to define an active relationship between BasicCalendarUS and Both fact tables. See Screen shot below. Your scenario does not call for any Dax aside from sum.

 

Screen Shot 2016-11-29 at 8.33.17 AM.png

View solution in original post

15 REPLIES 15
dkay84_PowerBI New Contributor
New Contributor

Re: creating a measure using to data tables and one calendar table

Within your measures that reference an inactive relationship, you need to use the function "USERELATIONSHIP"

 

See more here:

 

http://www.sqlbi.com/articles/userelationship-in-calculated-columns/

ccsrtw Regular Visitor
Regular Visitor

Re: creating a measure using to data tables and one calendar table

@dkay84_PowerBI, thanks for the quick response!

 

I've read through that post on USERELATIONSHIP, but I'm not sure it's the solution I need. If it is, I'm just not understanding how to correctly apply it.

 

I tried updating my close rate formula from:

 

DIVIDE([Deals with Demos], [Total Demos])

 

to:

 

CALCULATE(DIVIDE([Deals with Demos], [Total Demos]), USERELATIONSHIP('Demos'[OpenStamp], 'BasicCalendarUS'[DateKey]))

 

but this just gives me a blank as a result.

dkay84_PowerBI New Contributor
New Contributor

Re: creating a measure using to data tables and one calendar table

Try adjusting your formula to count the number of deals with demos and total demos:

 

CALCULATE(DIVIDE(COUNT([Deals with Demos]), COUNT([Total Demos])), USERELATIONSHIP('Demos'[OpenStamp], 'BasicCalendarUS'[DateKey]))

 

You may need to add a new column that returns a Y/N or True/False if the deal used a demo or not, and use that column for the Deals with Demos field (and update the COUNT function to COUNTA for text vs. numeric values).  I'm not sure of your data's structure, but in this case you are trying to divide the number of deals with demos by the total number of demos to get the close rate based on demos.  Thus, you need to tell the DIVIDE function that you are dealing with counts of those fields.

 

I'm not 100% sure this will solve your issue without having data to play with but give it a try

richbenmintz
Advisor

Re: creating a measure using to data tables and one calendar table

if you have two facts and two lookups, there should be no reason why you cannot have 2 active relationships from your dimensions to your facts, i would ensure that all of your relationships are single direction, 1->* from lookup to fact. once you have the appropriate relationships your measures should be easy.

ccsrtw Regular Visitor
Regular Visitor

Re: creating a measure using to data tables and one calendar table

@richbenmintz, can you expand a bit on your post?

 

I have one calendar table (BasicCalendarUS) and two data tables (Closed and Demos). Both data tables have a date field. I created a relationship between Closed and BasicCalendarUS - Single direction and (*:1) cardinality (one to many is not valid for this relationship). I then went on to create a relationship between Demos and BasicCalendarUS - Single direction and (*:1) cardinality (one to many is not valid here either). When I attempt to make this second relationship active as well, the error message regarding ambiguitiy pops up.

richbenmintz
Advisor

Re: creating a measure using to data tables and one calendar table

please attach your pbix file and i will have a look

ccsrtw Regular Visitor
Regular Visitor

Re: creating a measure using to data tables and one calendar table

@dkay84_PowerBI - No luck, wound up with a blank result. Will keep tinkering with that concept. Appreciate your ongoing help!

dkay84_PowerBI New Contributor
New Contributor

Re: creating a measure using to data tables and one calendar table

This may sound trivial but have you confirmed that your data types are set correctly?

dkay84_PowerBI New Contributor
New Contributor

Re: creating a measure using to data tables and one calendar table

Another thought: try making a 2 measures (counta or a calculate), one for number of deals with demos and one for demos.  Then, in your measure that calculates the close rate, use the intermediate measures you just created to perform the divide function.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 283 members 2,617 guests
Please welcome our newest community members: