sumx displays 0 in card even though there is a value

[jasonyeung87]

Hi,

I've created the following measure to check the date difference (in minutes) between 2 date fields in 2 tables. There is a 1 to 1 relationship between the 2 tables:

Measure_Difference_Lead_Opportunity_CreatedOn = SUMX
('Leads',
IF ( ISBLANK (DATEDIFF(SELECTEDVALUE(Leads[createdon]), SELECTEDVALUE(Opportunities[createdon]),MINUTE))
, 0.00
, DATEDIFF(SELECTEDVALUE(Leads[createdon]), SELECTEDVALUE(Opportunities[createdon]),MINUTE)
)
)

In the data, there could be Opportunities that have a NULL "createdon" date. In this case, I want to set the difference to 0.00. Otherwise it will return the difference between the dates.

When I use the card visualization and add this measure as a field, it will always show 0, even though there are some records with values of "createon" for both tables. (e.g. there is a value obtained from the "DATEDIFF" function).

I was wondering if the measure looks correct? I displayed the value in the table and the difference calculations appear correct.

Jason

[daxer-almighty]

@jasonyeung87 

Your measure might work OK (and it most likely is) but it's written in a way that it shouldn't be. It'll be much, much slower than if you had written it following Best Practices. One of the golden rules of efficient DAX programming reads: Never filter a table if you can filter a column. This rule has profound consequences with respect to performance. Please go to www.sqlbi.com and search for article(s) on this exact topic: The difference between column and table filters. Also, Alberto Ferrari has YT videos that explain why you shouldn't use table filters.

[daxer-almighty · Accepted solution]

First, if you have 2 tables that are truly connected with a 1-to-1 relationship, you should unify them into one table. Second, SELECTEDVALUE willl return BLANK if in the current context there are at least 2 different values visible. You should use SELECTEDVALUE only if you know upfront that only one value will be visible. DATEDIFF will return BLANK if at least one of the values fed into the function is BLANK.

[jasonyeung87]

@daxer-almighty ,

thansk for your help. I removed the SELECTEDVALUE and modified the measure. It seemed to have done the trick. I didn't combine the 2 tables, in case it's not a 1 to 1 relationship. The following measure does what I wanted:

Measure_Difference_Lead_Opportunity_CreatedOn = SUMX
(
CALCULATETABLE
('Leads',
FILTER('LeadStatusReasonCode','LeadStatusReasonCode'[LeadStatusReasonLabel] = "Qualified"),
FILTER('Leads', DATEDIFF('Leads'[createdon], TODAY(), month) <= 6),
FILTER('Leads',NOT(ISBLANK(Leads[qualifyingopportunityid.opportunityid])))
),
IF ( ISBLANK (RELATED(Opportunities[createdon]))
, 0
, DATEDIFF(Leads[createdon], RELATED(Opportunities[createdon]),MINUTE)
)
)

[jasonyeung87]

Hi,

I did some additional testing to the measure. When I display the results in a table visualization, the sum shows up correctly (e.g. if the difference between the 2 dates is an hour, the number returned is 60).

But in the card, it seems to be summing the first value in the IF clause (0.00) for all. It seems to ignore the second value).

[vanessafvg]

can you share some sample data?

[jasonyeung87]

This is a screenshot of some data displayed in the table

The first 2 records don't have a "createdon" value for "Opportunities", so the measure sets the differnece to 0. There are some records with the dates very close, so they'll also appear as 0. Two of the records differ by days, so the difference in minutes is big.

But it seems to always sum 0.