Forum Discussion

akay23's avatar
akay23
Icon for Helper I rankHelper I
4 years ago
Solved

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

Hi experts,

 

I have a table like;

 

CrrDepArrDate
aaxxxyyy1.08.2021
aayyyxxx2.08.2021
bbtttaaa3.08.2021
bbtttjjj4.08.2021
bbwwwttt5.08.2021
bbwwwttt6.08.2021
bbtttjjj7.08.2021
ccggghhh8.08.2021
ccjjjhhh9.08.2021

 

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

 

DepOps
xxxIN
yyyIN
tttOut
wwwOut
gggOut
jjjOut

 

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.

Need Help
  • SteveHailey's avatar
    SteveHailey
    4 years ago

    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

2 Replies

  • SteveHailey's avatar
    SteveHailey
    Icon for Solution Specialist rankSolution Specialist
    4 years ago

    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