TREATAS and nulls in one-to-many relationships

[biremedia]

Hi,

I am encountering some troubles while trying to remap my tables into a custom table.

In the attached screenshots you can see my example schema and my example values.

What I aim to is the following table:

CustomName	Amt	Description
Category type 1	970	All the reservations with the CategoryType 1 and (with Contract type differen to Contract type 1 or without contract)
CategoryType 2	165	All the reservations with the CategoryType 2 (with or without Contract)
CategoryType 3	125	All the reservations with the CategoryType 3 (with or without Contract)
Seasonal	100	All the reservations with the CategoryType 1 and Contract type 1
Total	1360

I have builded a custom table as follows:

and so I reached the following result:

with the following measure:

The problem here is that I lose all the reservations without contract.

How can I avoid this problem?

Thank you in advance!

[v-yueyunzh-msft]

Hi , @biremedia 

According to your desciption, you want to create a table like below. Right?

Here are the steps you can refer to :

(1)My test data is the same as your screenshoot.

(2)We can click "New Table" and enter this:

Table = var _t= SELECTCOLUMNS(ADDCOLUMNS('CategoryType' ,"Amt",var _id =[CategoryTypeId] return CALCULATE(SUM('Reservation'[Amount]) ,NOT( 'Reservation'[ContractId] = _id )   ) ),"Name",[Name],"Amt",[Amt])
var _t2 =FILTER('Reservation' , 'Reservation'[CategoryId]=1 && 'Reservation'[ContractId]=1)
var _t3=SELECTCOLUMNS(_t2,"Name" , "Seasonal" , "Amt",[Amount])
return
Union(_t,_t3)

(3)Then we can meet your need , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[biremedia]

Thank you for replying,

I am not sure to have fully understood your measure:

Table =
VAR _t =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            'CategoryType',
            "Amt",
                VAR _id = [CategoryTypeId]
                RETURN
                    CALCULATE (
                        SUM ( 'Reservation'[Amount] ),
                        NOT ( 'Reservation'[ContractId] = _id )
                    )
        ),
        "Name", [Name],
        "Amt", [Amt]
    )
VAR _t2 =
    FILTER (
        'Reservation',
        'Reservation'[CategoryId] = 1
            && 'Reservation'[ContractId] = 1
    )
VAR _t3 =
    SELECTCOLUMNS (
        _t2,
        "Name", "Seasonal",
        "Amt", [Amount]
    )
RETURN
    UNION (
        _t,
        _t3
    )

In the red part we are actually comparing two different things; the blue part instead is different from what I am looking for. I am sorry, maybe I was not clear, but I need to filter by CategoryType 1 and by ContractType 1, not by category 1 and contract 1. Into the Seasonal row I could have reservations with several different contracts and several different categories. I have not such information on the Reservation table but it is not a problem, I think that the blue part can be just replaced by:

CALCULATETABLE (
    Reservation,
    CategoryType[CategoryTypeId] = 1,
    ContractType[ContractTypeId] = 1
)

By the way I do not really want to create a result table, maybe a support table but the important think is that I can build a measure sensible to the CategoryType and ContractType tables lineage. This is just an example but my model is more complex and I need to embed that into a bigger environment.

I think that my proposed solution works fine, the big problem is that I cannot deal with invalid relationships like that between Contract and Reservation.

[v-yueyunzh-msft · Accepted solution]

Hi , @biremedia 

Thank you for the quick feedback, and sorry for misunderstanding in the previous reply and i update the dax and give you two solutions (table and measure) to you to refer to .
First , I wan to confirm the need you provide:

Based on my understanding ,  Only for the "Category type1" need to be judged? But i am not very sure about the "with Contract type different to Contract type 1 ", i see in your "Reservation" table is only have the [ContractId] field.

So in my logic, when the [CustomName]="Category type1" , the result i calculated is the condition isn't contained in the "'Contract'[ContractTypeId] = 1" . And other Category , the result i calculated is just the sum .

Here are the steps you can refer to , I'm not sure there will be of greate help but i wish it can give you some help.

The first method is return the table:

(1)We can click "New Table" and enter this:

Table = var _t= SELECTCOLUMNS(ADDCOLUMNS('CategoryType' ,"Amt",var _id =[CategoryTypeId] return IF(_id=1, CALCULATE(SUM('Reservation'[Amount]) ,NOT( 'Contract'[ContractTypeId] = _id )   ),CALCULATE(SUM('Reservation'[Amount])  ))  ),"Name",[Name],"Amt",[Amt])
var _t2 =CALCULATETABLE('Reservation' ,'Contract'[ContractTypeId]=1 , 'Category'[CategoryTypeId]=1)
var _t3=SELECTCOLUMNS(_t2,"Name" , "Seasonal" , "Amt",[Amount])
return
Union(_t,_t3)

Then we can get this table:

The second method is the measure:

(1)We need to create a table as row header in the visual , so we need to click "New Table" and enter this:

Table 2 = 
var _rows = COUNTROWS('CategoryType')+1
var _t1=ALL('CategoryType') 
var _t2= {(_rows,"Seasonal")}
return
UNION(_t1,_t2)

(2)We can click "New Measure" to create a measure :

Measure = var _current_catetypeId=SELECTEDVALUE('Table 2'[CategoryTypeId])
var _type1_Amt =SUMX( CALCULATETABLE('Reservation','CategoryType'[CategoryTypeId]=_current_catetypeId ,NOT('ContractType'[ContractTypeId]=_current_catetypeId)) , [Amount])
var _t2_Amt=SUMX( CALCULATETABLE('Reservation','CategoryType'[CategoryTypeId]=_current_catetypeId ) , [Amount])
var _seasonal = COUNTROWS('CategoryType')+1
var _seasonal_value =SUMX( CALCULATETABLE('Reservation' ,'Contract'[ContractTypeId]=1 , 'Category'[CategoryTypeId]=1) , [Amount])

return
IF(_current_catetypeId=_seasonal,_seasonal_value, IF(_current_catetypeId=1, _type1_Amt , _t2_Amt ))

(3)Then we can put the 'Table2'[Name] and measure on the viusal and we can get the table in the visual:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly