Actual Table V Virtual Table

[Anonymous]

Hi Experts

Slightly lost on this one, i cannot see where my error is in my DAX formula.

Here is the scenario.

i have the following table as a physical Table which i have entered into PBIX file.

Which using the following DAX give the correct results all the time.

__freq = 
VAR __table = SUMMARIZE(MK,[Value],"__ties",COUNT(MK[ID])-1)
VAR __table1 = ADDCOLUMNS(__table,"__freq",IF([__ties]=0,0,[__ties]*([__ties]+1)*(2*[__ties]+7)))
RETURN      
SUMX(__table1,[__freq])

However, when i try using a virtual table using the following DAX, i do not get the correct result all the time.

The difference here is we are using Date as a Index column where as above we have an index column not sure if this statement makes any difference at all.

__freq = 
VAR ComplaintsByFiscalMo = ADDCOLUMNS(VALUES(PMS_COMPLAINT[FISCAL_MON_START_DT]),"CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT)))
VAR __table = GROUPBY(ComplaintsByFiscalMo,[CountComplaints],"__ties",COUNTX(CURRENTGROUP(),[FISCAL_MON_START_DT]))
VAR __table1 = ADDCOLUMNS(__table,"__ties1",[__ties] - 1)
VAR __table2 = ADDCOLUMNS(__table1,"__freq",IF([__ties1]=0,0,[__ties1]*([__ties1]+1)*(2*[__ties1]+7)))
RETURN
SUMX(__table2,[__freq])

[v-shex-msft · Accepted solution]

HI @Anonymous ,

I'd like to suggest you to split your formulas and expand it to multiple formulas, then you can trace each step formulas to confirm which one get the wrong result.
In addition, you can also take a look at following blog about power bi time intelligence date dimension:

Power BI Date Dimension; Default or Custom? Is It Confusing?

Regards,

Xiaoxin Sheng

[Anonymous]

Xiaoxin Sheng firstly thanks for he feedback, excellent advise as always. thats what i did last night and found teh error.