Insert custom rows

[cosminc]

Hi all,

is it posible to add rows in a base like this:

ID	Year	Month	Client	Label	Value	concatenate ID&Client
1	2018	1	cosmin	A	1	1cosmin
1	2018	1	cosmin	B	1	1cosmin
1	2018	1	cosmin	C	1	1cosmin
1	2018	1	cosmin	D	0	1cosmin
2	2018	2	cosmin	A	0	2cosmin
2	2018	2	cosmin	B	1	2cosmin
2	2018	2	cosmin	C	1	2cosmin
2	2018	2	cosmin	D	0	2cosmin
3	2018	1	ana	A	0	3ana
3	2018	1	ana	B	0	3ana
3	2018	1	ana	C	0	3ana
3	2018	1	ana	D	1	3ana
4	2018	3	ana	A	0	4ana
4	2018	3	ana	B	1	4ana
4	2018	3	ana	C	1	4ana
4	2018	3	ana	D	1	4ana
add custom rows
1	2018	1	cosmin	Z	0	1cosmin
2	2018	2	cosmin	Z	0	2cosmin
3	2018	1	ana	Z	0	3ana
4	2018	3	ana	Z	1	4ana

If(Value for C=1 ann D=1 --value for new row is 1, otherwise os 0

the real base has thousands of rows and need to make something for each ID&Client

any idea?

Thanks,

Cosmin

[dax · Accepted solution]

Hi COSMINC,

You could try to new custom row table by DAX like below

custom rows =
SUMMARIZE (
    cutsom,
    cutsom[ID],
    cutsom[Year],
    cutsom[Month],
    cutsom[client],
    cutsom[concatenate ID&Client],
    "value", IF (
        CALCULATE (
            SUM ( cutsom[Value] ),
            FILTER (
                ALLEXCEPT ( cutsom, cutsom[ID] ),
                cutsom[Label] = "C"
                    || cutsom[Label] = "D"
            )
        ) = 2,
        1,
        0
    ),
    "Label", "Z"
)

Then  use selectcolumn  and union syntax to combine two table like below

Table 2 =
UNION (
    cutsom,
    SELECTCOLUMNS (
        'custom rows',
        "id", 'custom rows'[ID],
        "year", 'custom rows'[Year],
        "Month", 'custom rows'[Month],
        "client", 'custom rows'[client],
        "lable", 'custom rows'[Label],
        "value", 'custom rows'[value],
        "con", 'custom rows'[concatenate ID&Client]
    )
)

Best Regards,

Zoe Zhi

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

[cosminc]

Thanks,

very very helpfull!

Cosmin