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

Table Variable

Hi there,

 

I have a simplified data model with three tables.

 

1. account
---------

account.JPG


=======

2. opp( closedate dd/mm/yyyy)
----------
opp.JPG


===========
3. and a date table Calendar.


=======
relationship:
account[accountid] ---> opp[accountid]
Calendar[Date] ---> opp[closedate]
======
the problematic measure:
----------

test4:=
VAR k = 
ADDCOLUMNS( account,
            "date", MAXX( FILTER( opp,
                                  opp[accountid] = account[accountid]
                                  && opp[closedate] >= MIN( 'Calendar'[Date])
&& opp[closedate] <= MAX( 'Calendar'[Date])), opp[closedate])) RETURN CALCULATE( COUNTROWS(k), FILTER( k, [date]>= MIN( 'Calendar'[Date]) && [date] <= MAX('Calendar'[Date])))

======
expected result:
---------

expected result.JPG

however the result is:
--------
result.JPG

 

====
I can get the correct result by modifying the measure, but I would like to know what was going wrong with the problematic measure and what the different was between test4 and test2 below. Thank you.

test2:=
VAR k = ADDCOLUMNS( account,
                    "date", MAXX( FILTER( opp,
                                          opp[accountid] = account[accountid]
                                          && opp[closedate] >= MIN( 'Calendar'[Date])
                                          && opp[closedate] <= MAX( 'Calendar'[Date])),
                                  opp[closedate]))
RETURN
COUNTROWS(FILTER( k,
                  [date]>= MIN( 'Calendar'[Date])
                  && [date] <= MAX('Calendar'[Date])))

 

4 REPLIES 4
Community Support Team
Community Support Team

Re: Table Variable

Hi @rhlol ,

 

As I know CALCULATE function evaluates an expression in a context that is modified by the specified filters. Here the table k is not a fact table, so the issue occur.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Table Variable

Hi @rhlol ,

 

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rhlol Regular Visitor
Regular Visitor

Re: Table Variable

Hi Frank,

 

Thanks for your reply and the follow-up. I just got another question, kindly take a look at the expression below.

If the account table is used inside COUNTROWS instead of k, I can also get the correct result. As I read it from the some other posts, the original table has nothing to do with the table generated by ADDCOLUMNS. Is that true? If so, why the account table can be filtered?

 

RETURN
CALCULATE( COUNTROWS(account),
		FILTER( k,
			[date]>= MIN( 'Calendar'[Date])
			&& [date] <= MAX('Calendar'[Date])))

 

rhlol Regular Visitor
Regular Visitor

Re: Table Variable

Hi Frank,

 

Can you shed some light on my above questions? Thank you. Smiley Happy