How to select data from another table with a single slicer

[ddimitrov]

Hello PowerBI ninjas, gurus, delta forcers. 

I am a newcomer to PowerBI and need to design a PowerBI report for our project team. 
I tried several approaches to resolve an issue with a slicer for about a week, to no avail. 

Please assist with advise how to use a slicer on my report. I want to use the slicer so I can select and use data from another table and show it on current report. 

Here is the setup: 

-----------------------------------------------------------------------

•  2 tables: targets and actuals

Table: targets
Program | Quarter | Mdays
PROG1 | Q4 22 | 8
PROG2 | Q4 22 | 9
PROG3 | Q4 22 | 11
PROG1 | Q1 23 | 10
PROG2 | Q1 23 | 12
PROG3 | Q1 23 | 14
PROG1 | Q2 23 | 11
PROG2 | Q2 23 | 13
PROG3 | Q2 23 | 13
PROG1 | Q3 23 | 9
PROG2 | Q3 23 | 15
PROG3 | Q3 23 | 14
PROG1 | Q4 23 | 0
PROG2 | Q4 23 | 0
PROG3 | Q4 23 | 0

Table: actuals
Project ID | Project Name | Program | Subprogram | Quarter | Status | Mdays
1001 | PROJECT1 | PROG1 | SUB11 | Q4 22 | Closed | 2
1002 | PROJECT2 | PROG2 | SUB21 | Q4 22 | Closed | 2
1003 | PROJECT3 | PROG3 | SUB31 | Q4 22 | Closed | 2
1004 | PROJECT4 | PROG1 | SUB12 | Q4 22 | Closed | 3
1005 | PROJECT5 | PROG2 | SUB22 | Q4 22 | Closed | 3
1006 | PROJECT6 | PROG3 | SUB32 | Q4 22 | Closed | 3
1007 | PROJECT7 | PROG1 | SUB11 | Q1 23 | Closed | 2.5
1008 | PROJECT8 | PROG2 | SUB21 | Q1 23 | Closed | 2
1009 | PROJECT9 | PROG3 | SUB31 | Q1 23 | Closed | 3
1010 | PROJECT10 | PROG1 | SUB12 | Q1 23 | Closed | 2.5
1011 | PROJECT11 | PROG2 | SUB22 | Q1 23 | Closed | 2
1012 | PROJECT12 | PROG3 | SUB32 | Q1 23 | Closed | 3
1013 | PROJECT13 | PROG1 | SUB11 | Q2 23 | Closed | 2
1014 | PROJECT14 | PROG2 | SUB21 | Q2 23 | Closed | 3
1015 | PROJECT15 | PROG3 | SUB31 | Q2 23 | In Progress | 3
1016 | PROJECT16 | PROG1 | SUB12 | Q2 23 | In Progress | 3
1017 | PROJECT17 | PROG2 | SUB22 | Q2 23 | In Progress | 3
1018 | PROJECT18 | PROG3 | SUB32 | Q2 23 | In Progress | 3
1019 | PROJECT19 | PROG1 | SUB11 | Q3 23 | Pending | 1.5
1020 | PROJECT20 | PROG2 | SUB21 | Q3 23 | Pending | 2
1021 | PROJECT21 | PROG3 | SUB31 | Q3 23 | Pending | 2
1022 | PROJECT22 | PROG1 | SUB12 | Q3 23 | Pending | 1.5
1023 | PROJECT23 | PROG2 | SUB22 | Q3 23 | Pending | 2
1024 | PROJECT24 | PROG3 | SUB32 | Q3 23 | Pending | 2

Relationship
targets[Program] <---> actuals[Program]
Cardinality: Many to many (*.*)
Cross filter direction: Single (actuals filters targets)

-----------------------------------------------------------------------

• Report with 2 slicers, 2 matrices

Slicer: Program (actuals[Program])

Slicer: Quarter (actuals[Quarter])

Matrix: Targets vs Actuals

Rows:
actuals[Quarter]
actuals[Program]

Values:
targets[Mdays]
actuals[Mdays]
difference -- use a measure (need help how to calculate this measure, or column, or other way?)

Matrix: Projects

Rows:
actuals[Subprogram]
actuals[Project Name]

Values:
actuals[Quarter]
actuals[Status]
actuals[Mdays]

-----------------------------------------------------------------------

• Need help how to resolve: 

How to set (some or all of the below):

1. Slicer - which filters actuals[Quarter], based on this slicer, all Quarters columns are filled-in in the matrices in the report.  
2. Matrix: Targets vs Actuals, so it shows the exact number of targets[Mdays] for the selected slicers: Program and Quarter.
3. Something else, so I use only 1 slicer for Program and 1 slicer for Quarter, and Matrix: Targets vs Actuals shows targets[Mdays] for the selected Program and Quarter

In case the Slicer and Matrix: Targets vs Actuals are setup propertly, they should provide the following results for the 3 test cases listed below: 

-----------------------------------------------------------------------

• No slicers are selected

Slicer- Program: all

Slicer- Quarter: all

Matrix: Targets vs Actuals

Quarter/Program	Targets	Actuals	Difference
Q4 22		28	15	13
	PROG1	8	5	3
	PROG2	9	5	4
	PROG3	11	5	6
Q1 23		36	16	20
	PROG1	10	6	4
	PROG2	12	4	8
	PROG3	14	6	8
Q2 23		37	17	20
	PROG1	11	5	6
	PROG2	13	6	7
	PROG3	13	6	7
Q3 23		38	0	38
	PROG1	9	0	9
	PROG2	15	0	15
	PROG3	14	0	14
Q4 23		0	0	0
	PROG1	0	0	0
	PROG2	0	0	0
	PROG3	0	0	0

Matrix: Projects

Subprogram/Project	Quarter	Status	Mdays
SUB11				8
	PROJECT1	Q4 22	Closed	2
	PROJECT7	Q1 23	Closed	2.5
	PROJECT13	Q2 23	Closed	2
	PROJECT19	Q3 23	Pending	1.5
SUB12				10
	PROJECT4	Q4 22	Closed	3
	PROJECT10	Q1 23	Closed	2.5
	PROJECT16	Q2 23	In Progress	3
	PROJECT22	Q3 23	Pending	1.5
SUB21				9
	PROJECT2	Q4 22	Closed	2
	PROJECT8	Q1 23	Closed	2
	PROJECT14	Q2 23	Closed	3
	PROJECT20	Q3 23	Pending	2
SUB22				10
	PROJECT5	Q4 22	Closed	3
	PROJECT11	Q1 23	Closed	2
	PROJECT17	Q2 23	In Progress	3
	PROJECT23	Q3 23	Pending	2
SUB31				10
	PROJECT3	Q4 22	Closed	2
	PROJECT9	Q1 23	Closed	3
	PROJECT15	Q2 23	In Progress	3
	PROJECT21	Q3 23	Pending	2
SUB32				11
	PROJECT6	Q4 22	Closed	3
	PROJECT12	Q1 23	Closed	3
	PROJECT18	Q2 23	In Progress	3
	PROJECT24	Q3 23	Pending	2

-----------------------------------------------------------------------

• Slicer program selected only

Slicer-Program: PROG2

Slicer-Quarter: all

Matrix: Targets vs Actuals

Quarter/Program	Targets	Actuals	Difference
Q4 22		9	5	4
	PROG2	9	5	4
Q1 23		36	4	32
	PROG2	12	4	8
Q2 23		37	6	31
	PROG2	13	6	7
Q3 23		38	0	38
	PROG2	15	0	15
Q4 23		0	0	0
	PROG2	0	0	0

Matrix: Projects

Subprogram/Project	Quarter	Status	Mdays
SUB21				9
	PROJECT2	Q4 22	Closed	2
	PROJECT8	Q1 23	Closed	2
	PROJECT14	Q2 23	Closed	3
	PROJECT20	Q3 23	Pending	2
SUB22				10
	PROJECT5	Q4 22	Closed	3
	PROJECT11	Q1 23	Closed	2
	PROJECT17	Q2 23	In Progress	3
	PROJECT23	Q3 23	Pending	2

-----------------------------------------------------------------------

• Slicer quarter selected only

Slicer-Program: all

Slicer-Quarter: Q2 23

Matrix: Targets vs Actuals

Quarter/Program	Targets	Actuals	Difference
Q2 23		37	17	20
	PROG1	11	5	6
	PROG2	13	6	7
	PROG3	13	6	7

Matrix: Projects

Subprogram/Project	Quarter	Status	Mdays
SUB11				2
	PROJECT13	Q2 23	Closed	2
SUB12				3
	PROJECT16	Q2 23	In Progress	3
SUB21				3
	PROJECT14	Q2 23	Closed	3
SUB22				3
	PROJECT17	Q2 23	In Progress	3
SUB31				3
	PROJECT15	Q2 23	In Progress	3
SUB32				3
	PROJECT18	Q2 23	In Progress	3

-----------------------------------------------------------------------

Thanks,

Drago

[v-yanjiang-msft · Accepted solution]

Hi @ddimitrov ,

According to your description, here's my solution. Create two measures.

MdaysTarget =
IF (
    ISFILTERED ( Actuals[Program] ),
    SUMX (
        FILTER (
            ALL ( 'Targets' ),
            'Targets'[Quarter] = SELECTEDVALUE ( Actuals[Quarter] )
                && 'Targets'[Program] = SELECTEDVALUE ( 'Actuals'[Program] )
        ),
        'Targets'[Mdays]
    ),
    IF (
        ISFILTERED ( 'Actuals'[Quarter] ),
        SUMX (
            FILTER (
                ALL ( 'Targets' ),
                'Targets'[Quarter] = SELECTEDVALUE ( Actuals[Quarter] )
            ),
            'Targets'[Mdays]
        ),
        SUM ( 'Targets'[Mdays] )
    )
)

Difference =
'Actuals'[MdaysTarget] - SUM ( 'Actuals'[ Mdays] )

Get the result.

I attach the file below for your reference.

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

Best regards,

Community Support Team_yanjiang

[ddimitrov]

@v-yanjiang-msft 

I tested it and it worked. 

Your solution is more elengant than mine. Congratulations 🙂 

Thanks a lot. 

[ddimitrov]

Thanks @v-pn 

I will test it now. 

Just for the record, last night I did a final tweak and it worked for my report: 

Mdays-Ms= IF(
    ISINSCOPE('actuals'[Quarter]),
    SUMX(
        FILTER(
            targets,
            'targets'[Quarter] = SELECTEDVALUE('actuals'[Quarter])),
        'targets'[Mdays]),
    BLANK()
    )

the only change is replaced the hardcoded quarter value with: 

SELECTEDVALUE('actuals'[Quarter])  

[amitchandak]

@ddimitrov , I such cases, it always advisable to have common dimension tables 

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...

Power BI- Power Query: When I asked you to create common tables: https://youtu.be/PqfGW6pl1Sw

[ddimitrov]

@amitchandak 

In this particular issue, with the described setup above, I am trying to resolve the following: 

• for Slicer-Quarter -- I use: actuals[Quarter]
• in table: Actuals -- I created a measure: actuals[Mdays-Ms]
• in Matrix: Targets vs Actuals -- I added measure: targets[Mdays-Ms]
• this is the logic/code I use for the measure: 

Mdays-Ms= IF(
    ISINSCOPE('actuals'[Quarter]),
    SUMX(
        FILTER(
            targets,
            'targets'[Quarter] = "Q2 23"),
        'targets'[Mdays]),
    BLANK()
    )

However, this logic works only for hardcoded value for quarter. I would like the hardcoded: "Q2 23" to be the Slicer-Quarter selection.

What is the proper logic/code I use for measure: actuals[Mdays-Ms] so it gets:

• for Quarter selected -- exact values for programs from column: targets[Mdays]
• for Program selected -- exact values for Quarters from column: targets[Mdays]
• for Quarter and Program selected -- exact value for the program and quarter from column: targets[Mdays]
• for nothing selected -- exact values for each program and each quarter

[ddimitrov]

Thanks for the advice, Amit. 

I will test it in coming days and will share feedback.