I want to enter data from multiple fields in a single column but on different rows

[Pp110784]

I have a table with clients, their plans and investments data. Their hierarchy is client --> plans --> investments. So a client has multiple plans under them and a plan has multiple investments under it. We consolidate this data from various sources (let's say A, B and C). So a client data can be from source A, its 1 plan's data can be from source A, and 2nd plan's data can be from source B. 2 investments under plan 1 can be from source A, 2 from source B and 2 from source C. Plan 2 can have all its investments from source B. I want to create a report with a visual like this:

Plan Sponsor Name	Source
ABC	A
Plan 1	A
Investment 1.1	A
Investment 1.2	A
Investment 1.3	B
Investment 1.4	B
Investment 1.5	C
Investment 1.6	C
Plan 2	B
Investment 2.1	B
Investment 2.2	B
Investment 2.3	B

Is there a way I can create this kind of visual in power BI? I tried looking at the forum posts, but couldn't find a similar post.

[Pp110784 · Accepted solution]

This one seems to be working:

Measure =

IF (

    ( ISINSCOPE ( 'Sheet1'[Investment Name] ) ),

    MIN('Sheet1'[Investment Source]),

    IF (

        ISINSCOPE ( 'Sheet1'[Plan Name] ),

        min('Sheet1'[Plan Source]),

        min('Sheet1'[Plan Sponsor Source])

    )

)

My input data is this:

Plan Sponsor Name	Plan Sponsor Source	Plan Name	Plan Source	Investment Name	Investment Source
ABC	A	Plan 1	A	Investment 1.1	A
ABC	A	Plan 1	A	Investment 1.2	A
ABC	A	Plan 1	A	Investment 1.3	B
ABC	A	Plan 1	A	Investment 1.4	B
ABC	A	Plan 1	A	Investment 1.5	C
ABC	A	Plan 1	A	Investment 1.6	C
ABC	A	Plan 2	B	Investment 2.1	B
ABC	A	Plan 2	B	Investment 2.2	B
ABC	A	Plan 2	B	Investment 2.3	B
CDE	A	Plan 3	B	Investment 3.1	B
CDE	A	Plan 3	B	Investment 3.2	B
EFG	C	Plan 4	C	Investment 4.1	C
EFG	C	Plan 4	C	Investment 4.2	C

Using this formula, I can see the desired output:

Thank you @v-binbinyu-msft. Your solution helped me reach this solution.

[Pp110784]

@Greg_Deckler , @v-binbinyu-msft 

I tried this formula:

measure ds2 = switch( true,

            isinscope ('Query1'[INVESTMENT_NAME]), ('Query1'[INV_SRC_CD]),

            isinscope ('Query1'[PLAN_NAME]), ('Query1'[PLAN_SRC_CD]),

            isinscope ('Query1'[PLAN_SPONSOR_NAME]), ('Query1'[PLAN_SPONSOR_SRC_CD]))

But this is giving me the error 'A single value for the column 'INV_SRC_CD' in table 'Query1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregartion such as min, max, count, or sum to get a single result. Can you please suggest a way to get rid of this error? Looks like my formula doesn't work on row level.

[Greg_Deckler]

@Pp110784 Maybe. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Pp110784]

Sorry I did not mention my input data format:

So my input data looks like this:

Plan Sponsor Name	Plan Sponsor Source	Plan Name	Plan Source	Investment Name	Investment Source
ABC	A	Plan 1	A	Investment 1.1	A
ABC	A	Plan 1	A	Investment 1.2	A
ABC	A	Plan 1	A	Investment 1.3	B
ABC	A	Plan 1	A	Investment 1.4	B
ABC	A	Plan 1	A	Investment 1.5	C
ABC	A	Plan 1	A	Investment 1.6	C
ABC	A	Plan 2	B	Investment 2.1	B
ABC	A	Plan 2	B	Investment 2.2	B
ABC	A	Plan 2	B	Investment 2.3	B

What I want to show in the report is like this:

Plan Sponsor Name	Source
ABC	A
Plan 1	A
Investment 1.1	A
Investment 1.2	A
Investment 1.3	B
Investment 1.4	B
Investment 1.5	C
Investment 1.6	C
Plan 2	B
Investment 2.1	B
Investment 2.2	B
Investment 2.3	B

[v-binbinyu-msft]

HI @Pp110784 ,

First of all, many thanks to @Greg_Deckler  for your very quick and effective replies.

Please try below steps:

1. below is my test table

Table:

2. create a measure with below dax formula

MEASURE =
IF (
    ISINSCOPE ( 'Table'[Plan Sponsor Name] ) || ISINSCOPE ( 'Table'[Plan Name] )
        || ISINSCOPE ( 'Table'[Investment Name] ),
    MIN ( 'Table'[Investment Source] )
)

3. add a matrix visual with fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pp110784]

Hi @v-binbinyu-msft . Thank you so much for the solution. This works. But when I added the following data to my dataset:

Plan Sponsor Name	Plan Sponsor Source	Plan Name	Plan Source	Investment Name	Investment Source
CDE	A	Plan 3	B	Investment 3.1	B
CDE	A	Plan 3	B	Investment 3.2	B

I got this result:

Can you please explain to me what your formula is doing? So I can also try to edit it.

[v-binbinyu-msft]

Hi @Pp110784 ,

Why it should be A ?  I am confused.

My dax formula is very simple, Show the 'Table'[Investment Source] value in matrix visual except in Total.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pp110784]

Because for the plan sponsor CDE the source is A, but the plan & investment source for it is B. So the plan sponsor source should be shown as A, and the plan & investment will have the source of B.

Pasting the input data here again for your quick reference:

Plan Sponsor Name | Plan Sponsor Source | Plan Name | Plan Source | Investment Name | Investment Source

CDE | A | Plan 3 | B | Investment 3.1 | B

CDE | A | Plan 3 | B | Investment 3.2 | B