topic Re: TREATAS vs USERELATIONSHIP - Both incorrect?! in DAX Commands and Tips

TREATAS vs USERELATIONSHIP - Both incorrect?!

scriptpup — Thu, 01 Oct 2020 19:23:39 GMT

Can anyone explain the differences between TREATAS and USERELATIONSHIP in a practical application? The key difference as far as I can tell is USERELATIONSHIP needs an inactive relationship defined in the model to work.

There's also some other difference that I'm not able to pin down, but the same calculation using USERELATIONSHIP and TREATAS results in completely different results. For example:

DEFINE MEASURE Transactional[test_taskJoin2] = CALCULATE(DISTINCTCOUNT(Transactional[Profile Task Id]) , TREATAS( VALUES(Transactional[Profile Activity Owner ID]), Person[SRC_PRSN_HIST_GEN_ID])) MEASURE Transactional[test_taskJoin] = CALCULATE([Transactional Open], USERELATIONSHIP( Transactional[Profile Activity Owner ID], Person[SRC_PRSN_HIST_GEN_ID])) EVALUATE SUMMARIZE( Transactional, Person[Level 2 Manager] ,"Test (UseRelationship)",Transactional[test_taskJoin] ,"Test (TreatAs)", Transactional[test_taskJoin2] ) ORDER BY Transactional[test_taskJoin] desc

Results in:

Then at the transactional level, filtering to a single L2 Manager (The fourth one down from the top) TREATAS matches what USERELATIONSHIP rolls up to, but USERELATIONSHIP doesn't match itself.

DEFINE MEASURE Transactional[test_taskJoin] = CALCULATE([Transactional Open], USERELATIONSHIP( Transactional[Profile Activity Owner ID], Person[SRC_PRSN_HIST_GEN_ID])) MEASURE Transactional[test_taskJoin2] = CALCULATE([Transactional Open], TREATAS( VALUES(Transactional[Profile Activity Owner ID]), Person[SRC_PRSN_HIST_GEN_ID]) ) VAR __DS0FilterTable = TREATAS( {"L2 Manager Name" }, Person[Level 2 Manager] ) VAR __DS0Core = SUMMARIZECOLUMNS( Agent[Agent Name], Transactional[Profile Task Id], Transactional[Case], __DS0FilterTable, "Test Task (UseRelationship)", Transactional[test_taskJoin], "Test Task (TreatAS)", Transactional[test_taskJoin2] ) EVALUATE __DS0Core

Summing the two measures at the transactional level in excel ends up with:

I am completely confused as to why neither of these methods seem consistent or accurate.

Any insight would be greatly appreciated.

Re: TREATAS vs USERELATIONSHIP - Both incorrect?!

MattAllington — Thu, 01 Oct 2020 20:56:43 GMT

Your treatas formula is back to front

TREATAS (
                VALUES ( Transactional[Profile Activity Owner ID] ),
                Person[SRC_PRSN_HIST_GEN_ID]
            )

have a read of my article here https://exceleratorbi.com.au/virtual-filters-using-treatas/

Re: TREATAS vs USERELATIONSHIP - Both incorrect?!

scriptpup — Fri, 02 Oct 2020 14:29:00 GMT

Hi Matt,

My mistake with the pasted formula, I actually tried it both ways but it comes back with the same results regardless of which sides I use.

I've read that column in the past, and several others that are similar. The problem I'm having is I don't understand why the two functions would result in something completely different with the same filter-set, same relationship defined, and neither of them are correct.

Re: TREATAS vs USERELATIONSHIP - Both incorrect?!

MattAllington — Fri, 02 Oct 2020 20:03:47 GMT

I stopped looking after I saw the first issue. I would doubt if summarize or SUMMARIZECOLUMNS are appropriate in this case. Both of these functions leverage the existing active physical relationships in the model to build the starting table. I suggest you don't use either of these functions and build the table yourself instead. Try this

DEFINE MEASURE Transactional[test_taskJoin2] = CALCULATE ( DISTINCTCOUNT ( Transactional[Profile Task Id] ), TREATAS ( VALUES ( Person[SRC_PRSN_HIST_GEN_ID] ), Transactional[Profile Activity Owner ID], Person[SRC_PRSN_HIST_GEN_ID] ) ) MEASURE Transactional[test_taskJoin] = CALCULATE ( [Transactional Open], USERELATIONSHIP ( Transactional[Profile Activity Owner ID], Person[SRC_PRSN_HIST_GEN_ID] ) ) EVALUATE ADDCOLUMNS ( ALL ( Person[Level 2 Manager] ), "Test (UseRelationship)", Transactional[test_taskJoin], "Test (TreatAs)", Transactional[test_taskJoin2] ) ORDER BY Transactional[test_taskJoin] DESC

Re: TREATAS vs USERELATIONSHIP - Both incorrect?!

AntrikshSharma — Sat, 03 Oct 2020 05:54:32 GMT

scriptpup can you post your Power BI file or the snapshot of the diagram view?