Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
This one seems to be working:
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 |
This one seems to be working:
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 |
@Greg_Deckler , @v-binbinyu-msft
I tried this formula:
@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.
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 |
| A |
| A |
| B |
| B |
| C |
| C |
Plan 2 | B |
| B |
| B |
| B |
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.
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.
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.
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
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |