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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ccsrtw
Helper I
Helper I

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

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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


View solution in original post

15 REPLIES 15

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/

@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.

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

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

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.

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

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.



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


@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.

to simplify for me, you have fact table 1 realated to your date table and fact table 2 related to your date table, both facts should be the many side of the relationship. depending on the cardinality of your facts you may actually have created the relationship backwards. If you have a 1 ->1 relationship based on the data in the tables you may have created the relationship 1->* fact to dimension. would be very easy for me to diagnose if i had your pbix file. 



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


@dkay84_PowerBI - data types are set correctly, double-checked. Regarding your last post - those are already taken care of via calculate(countrows( and then a logic statement as the filter (was a demo used or not).

 

@richbenmintz - below is a simplified version of the file, but all of the data, relationships, calculations, etc. are conceptually the same. Hope this helps.

 

https://www.dropbox.com/s/4itq9hjf1z7fk06/Shared%20View.pbix?dl=0

Hi @ccsrtw,

 

After looking over the shared sample pbix file, I think you should use the formula below to calculate "Total Demos". After changing that, you should get the proper result.Smiley Happy

Total Demos =
CALCULATE (
    COUNTROWS ( Demos ),
    USERELATIONSHIP ( BasicCalendarUS[DateKey], Demos[Open_Stamp__c] )
)

totaldemos.PNG

 

Regards

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



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


you will also find the pbix with updates here:

 

https://1drv.ms/u/s!AhCeuF2piSWMgb90JcPtZyimJ-HWNw



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Thank you @richbenmintz, @v-ljerr-msft, and @dkay84_PowerBI! Really appreciate all of the assistance, awesome community.

please attach your pbix file and i will have a look



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.