cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
richbenmintz
Advisor

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

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. 

ccsrtw Regular Visitor
Regular Visitor

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

@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

v-ljerr-msft Super Contributor
Super Contributor

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

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

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

richbenmintz
Advisor

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

you will also find the pbix with updates here:

 

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

ccsrtw Regular Visitor
Regular Visitor

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

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