Treatas DAX function not working as expected, It is returning complete blank column

Question

I trying to understand Treatas DAX function. There are two tables Assets and Ticket. Ticket table has parent and child relationship. For each value of Asset[AssetKey], I want to calculate count of childs in Ticket table. There is two relationships between these tables. One active and one inactive.

The Problem: When I use Treatas function complete measure Number of Child is retured blank. I used the formula -

Number of Child = CALCULATE(COUNT(Tickets[AssetKey]),TREATAS(SUMMARIZE(Asset,Asset[AssetKey]),Tickets[ParentId]))

To replicate the scenario follow the below steps:

Step 1: create table Asset:

Asset = DATATABLE("AssetKey",INTEGER,"Name",STRING,{{1,"Australia"},
{2,"Belgium"},
{3,"Canada"},
{4,"Denmark"},
{5,"England"}})

Create table Ticket

Tickets = DATATABLE("AssetKey",INTEGER,"ParentId",INTEGER,"TicketKey",INTEGER,{{3,1,1},
{1,Blank(),1},
{3,1,3},
{2,Blank(),4},
{4,2,5},
{3,1,6},
{2,Blank(),7},
{4,2,8},
{1,Blank(),9},
{5,2,10}})

Step2 : create relationship between Assets and Ticket table(one to many) on column AssetKey.

Step3: Now create the below Measures -

Number Of Tickets = COUNT(Tickets[TicketKey])

Number of Child = CALCULATE(COUNT(Tickets[AssetKey]),TREATAS(SUMMARIZE(Asset,Asset[AssetKey]),Tickets[ParentId]))

Now the problem: Why the Number of Child column comes out to be blank.

The expected output is :

Aldert Aldert · Accepted Answer · 2020-08-16T16:23:13

Your problem is not the TREATAS but the SUMMARIZE. TREATAS expects table input so you summarized.

Try the following:

summarizedAsset = SUMMARIZE(Asset,Asset[AssetKey])

This returns you 1,2,3,4,5. Logic because this is what you asked it to do. Now TREATAS is going to do the same on the table Ticket. So it returns a table with one column values "empty",1,2. Exactly what we can expect. Next thing you do a COUNT on AssetKey, this table just created does not have this column so it returns empty.

What you are looking for is a column what is calculating the children:

Number of Child = 
var Akey = Asset[AssetKey]
return CALCULATE(COUNT(Tickets[ParentId]), filter(Tickets, Akey = Tickets[ParentId]))

This returns exactly what you where looking for.

PS: 10 points, you did an excellent job on the question asking, easy for others to reproduce. You work as a pro!!

Treatas DAX function not working as expected, It is returning complete blank column

1 Answers