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!
Solved! Go to Solution.
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.
Within your measures that reference an inactive relationship, you need to use the function "USERELATIONSHIP"
See more here:
@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])
CALCULATE(DIVIDE([Deals with Demos], [Total Demos]), USERELATIONSHIP('Demos'[OpenStamp], 'BasicCalendarUS'[DateKey]))
but this just gives me a blank as a result.
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
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.
@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.
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.