How can I create a calculated table including values column and if statement?

[akay23]

Hi experts,

I have a table like;

Crr	Dep	Arr	Date
aa	xxx	yyy	1.08.2021
aa	yyy	xxx	2.08.2021
bb	ttt	aaa	3.08.2021
bb	ttt	jjj	4.08.2021
bb	www	ttt	5.08.2021
bb	www	ttt	6.08.2021
bb	ttt	jjj	7.08.2021
cc	ggg	hhh	8.08.2021
cc	jjj	hhh	9.08.2021

And I want a referance table for Crr (aa), calculated from this table like;

Dep	Ops
xxx	IN
yyy	IN
ttt	Out
www	Out
ggg	Out
jjj	Out

I tried for this something like but I realized that I cannot create like this;

Define

VAR t1 = SUMMARIZE(
Data,
Data[Dep])

VAR t2 = SUMMARIZE(FILTER(Data,Data[Crr] = "aa"),
Data[Dep])

VAR t3 = ADDCOLUMNS(t1,"Ops", IF(..........

Return

How can I create a calculated table for this.

Thank You.

[SteveHailey · Accepted solution]

Hello @akay23.

You could use:

New table with SUMMARIZECOLUMNS = 
SUMMARIZECOLUMNS(
    'Data'[Dep],
    "Ops",
        IF(
            COUNTROWS( CALCULATETABLE( 'Data', 'Data'[Crr] = "aa" ) )
                > 0,
            "IN",
            "Out"
        )
)

Or you could use:

New table with ADDCOLUMNS and SUMMARIZE = 
ADDCOLUMNS(
    SUMMARIZE( 'Data', 'Data'[Dep] ),
    "Ops",
        IF(
            COUNTROWS( CALCULATETABLE( 'Data', 'Data'[Crr] = "aa" ) )
                > 0,
            "IN",
            "Out"
        )
)

I attached a .pbix file here in case it's helpful.

-Steve

[akay23]

Thank you for your help.

Appreciate @SteveHailey