Forum Discussion

scriptpup's avatar
scriptpup
Frequent Visitor
5 years ago

TREATAS vs USERELATIONSHIP - Both incorrect?!

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...
MattAllington's avatar
MattAllington
Community Champion
5 years ago

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/

 

scriptpup's avatar
scriptpup
Frequent Visitor
5 years ago

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.

  • MattAllington's avatar
    MattAllington
    Community Champion
    5 years ago

    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