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 actualsTable: targetsProgram | Quarter | MdaysPROG1 | Q4 22 | 8PROG2 | Q4 22 | 9PROG3 | Q4 22 | 11PROG1 | Q1 23 | 10PROG2 | Q1 23 | 12PROG3 | Q1 23 | 14PROG1 | Q2 23 | 11PROG2 | Q2 23 | 13PROG3 | Q2 23 | 13PROG1 | Q3 23 | 9PROG2 | Q3 23 | 15PROG3 | Q3 23 | 14PROG1 | Q4 23 | 0PROG2 | Q4 23 | 0PROG3 | Q4 23 | 0Table: actualsProject ID | Project Name | Program | Subprogram | Quarter | Status | Mdays1001 | PROJECT1 | PROG1 | SUB11 | Q4 22 | Closed | 21002 | PROJECT2 | PROG2 | SUB21 | Q4 22 | Closed | 21003 | PROJECT3 | PROG3 | SUB31 | Q4 22 | Closed | 21004 | PROJECT4 | PROG1 | SUB12 | Q4 22 | Closed | 31005 | PROJECT5 | PROG2 | SUB22 | Q4 22 | Closed | 31006 | PROJECT6 | PROG3 | SUB32 | Q4 22 | Closed | 31007 | PROJECT7 | PROG1 | SUB11 | Q1 23 | Closed | 2.51008 | PROJECT8 | PROG2 | SUB21 | Q1 23 | Closed | 21009 | PROJECT9 | PROG3 | SUB31 | Q1 23 | Closed | 31010 | PROJECT10 | PROG1 | SUB12 | Q1 23 | Closed | 2.51011 | PROJECT11 | PROG2 | SUB22 | Q1 23 | Closed | 21012 | PROJECT12 | PROG3 | SUB32 | Q1 23 | Closed | 31013 | PROJECT13 | PROG1 | SUB11 | Q2 23 | Closed | 21014 | PROJECT14 | PROG2 | SUB21 | Q2 23 | Closed | 31015 | PROJECT15 | PROG3 | SUB31 | Q2 23 | In Progress | 31016 | PROJECT16 | PROG1 | SUB12 | Q2 23 | In Progress | 31017 | PROJECT17 | PROG2 | SUB22 | Q2 23 | In Progress | 31018 | PROJECT18 | PROG3 | SUB32 | Q2 23 | In Progress | 31019 | PROJECT19 | PROG1 | SUB11 | Q3 23 | Pending | 1.51020 | PROJECT20 | PROG2 | SUB21 | Q3 23 | Pending | 21021 | PROJECT21 | PROG3 | SUB31 | Q3 23 | Pending | 21022 | PROJECT22 | PROG1 | SUB12 | Q3 23 | Pending | 1.51023 | PROJECT23 | PROG2 | SUB22 | Q3 23 | Pending | 21024 | PROJECT24 | PROG3 | SUB32 | Q3 23 | Pending | 2Relationshiptargets[Program] <---> actuals[Program]Cardinality: Many to many (*.*)Cross filter direction: Single (actuals filters targets)-----------------------------------------------------------------------Report with 2 slicers, 2 matricesSlicer: Program (actuals[Program]) Slicer: Quarter (actuals[Quarter]) Matrix: Targets vs ActualsRows: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: ProjectsRows: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):Slicer - which filters actuals[Quarter], based on this slicer, all Quarters columns are filled-in in the matrices in the report. Matrix: Targets vs Actuals, so it shows the exact number of targets[Mdays] for the selected slicers: Program and Quarter.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 selectedSlicer- Program: allSlicer- Quarter: all Matrix: Targets vs ActualsQuarter/ProgramTargetsActualsDifferenceQ4 22 281513 PROG1853 PROG2954 PROG31156Q1 23 361620 PROG11064 PROG21248 PROG31468Q2 23 371720 PROG11156 PROG21367 PROG31367Q3 23 38038 PROG1909 PROG215015 PROG314014Q4 23 000 PROG1000 PROG2000 PROG3000 Matrix: ProjectsSubprogram/ProjectQuarterStatusMdaysSUB11 8 PROJECT1Q4 22Closed2 PROJECT7Q1 23Closed2.5 PROJECT13Q2 23Closed2 PROJECT19Q3 23Pending1.5SUB12 10 PROJECT4Q4 22Closed3 PROJECT10Q1 23Closed2.5 PROJECT16Q2 23In Progress3 PROJECT22Q3 23Pending1.5SUB21 9 PROJECT2Q4 22Closed2 PROJECT8Q1 23Closed2 PROJECT14Q2 23Closed3 PROJECT20Q3 23Pending2SUB22 10 PROJECT5Q4 22Closed3 PROJECT11Q1 23Closed2 PROJECT17Q2 23In Progress3 PROJECT23Q3 23Pending2SUB31 10 PROJECT3Q4 22Closed2 PROJECT9Q1 23Closed3 PROJECT15Q2 23In Progress3 PROJECT21Q3 23Pending2SUB32 11 PROJECT6Q4 22Closed3 PROJECT12Q1 23Closed3 PROJECT18Q2 23In Progress3 PROJECT24Q3 23Pending2 -----------------------------------------------------------------------Slicer program selected onlySlicer-Program: PROG2Slicer-Quarter: all Matrix: Targets vs ActualsQuarter/ProgramTargetsActualsDifferenceQ4 22 954 PROG2954Q1 23 36432 PROG21248Q2 23 37631 PROG21367Q3 23 38038 PROG215015Q4 23 000 PROG2000 Matrix: ProjectsSubprogram/ProjectQuarterStatusMdaysSUB21 9 PROJECT2Q4 22Closed2 PROJECT8Q1 23Closed2 PROJECT14Q2 23Closed3 PROJECT20Q3 23Pending2SUB22 10 PROJECT5Q4 22Closed3 PROJECT11Q1 23Closed2 PROJECT17Q2 23In Progress3 PROJECT23Q3 23Pending2 -----------------------------------------------------------------------Slicer quarter selected onlySlicer-Program: allSlicer-Quarter: Q2 23 Matrix: Targets vs ActualsQuarter/ProgramTargetsActualsDifferenceQ2 23 371720 PROG11156 PROG21367 PROG31367 Matrix: ProjectsSubprogram/ProjectQuarterStatusMdaysSUB11 2 PROJECT13Q2 23Closed2SUB12 3 PROJECT16Q2 23In Progress3SUB21 3 PROJECT14Q2 23Closed3SUB22 3 PROJECT17Q2 23In Progress3SUB31 3 PROJECT15Q2 23In Progress3SUB32 3 PROJECT18Q2 23In Progress3 ----------------------------------------------------------------------- Thanks,Drago

Forum Discussion

Regular Visitor

3 years ago

Solved

How to select data from another table with a single slicer

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):

Slicer - which filters actuals[Quarter], based on this slicer, all Quarters columns are filled-in in the matrices in the report.
Matrix: Targets vs Actuals, so it shows the exact number of targets[Mdays] for the selected slicers: Program and Quarter.
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

Need Help

v-yanjiang-msft

3 years ago

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

How to select data from another table with a single slicer.pbix27 KB

6 Replies

amitchandak
Super User
3 years ago
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-solution-8e3eccb41bda

Power BI- Power Query: When I asked you to create common tables: https://youtu.be/PqfGW6pl1Sw
- ddimitrov
  Regular Visitor
  3 years ago
  Thanks for the advice, Amit.
  I will test it in coming days and will share feedback.
- ddimitrov
  Regular Visitor
  3 years ago
  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
v-yanjiang-msft
Community Support
3 years ago
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
How to select data from another table with a single slicer.pbix27 KB
- ddimitrov
  Regular Visitor
  3 years ago
  v-yanjiang-msft
  I tested it and it worked.
  Your solution is more elengant than mine. Congratulations 🙂
  Thanks a lot.
- ddimitrov
  Regular Visitor
  3 years ago
  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])

Forum Discussion

How to select data from another table with a single slicer

6 Replies

Related content

Single select slicer

Slicer Single select

Single Selection of aggregated columns

button slicer single select allows for multiple selections

ROW LEVEL SECURITY with forced selection slicer single select