Forum Discussion

Bbeckert's avatar
Bbeckert
New Member
2 years ago
Solved

Value Lookup Based on Slicer

I am semi-new to PowerBI. I've managed to teach myself basic features, but am now out of my depth (I am not a developer). 

 

Objective: Generate a cost estimate per sprint for each software development team under my purview that looks something like this (all dummy data):

 

User inputs: A development start date, number of sprints needed to do the work, and team assigned to the work. 

So for the above example, the user would have selected a Development Start Date of 6/3/24, estimated only 2 sprints were needed, and assigned the work to Team 01. 

 

I created an index column that assigns a unique ID to each possible Sprint Start date from which users may choose.

 

I then have a calculated column in my Dev Timeline Select table that assigns a Sprint Series number so that I can determine sprint intervals. For example, if the user chooses 6/3 as the Development Start Date, then Start Date for Sprint 1 is 6/3, Sprint 2 is 6/17, etc. But if the user chooses 6/4 as the Development Start Date, then the Start Date for Sprint 1 is 6/4 and Sprint 2 is 6/18. 

 

I have another table (Team Cost) that shows the cost of each team over these intervals (again, all dummy data).

 

In my head, the workflow is:

User selects Team from Slicer 1; User selects Dev Start Date from Slicer 2 --> Look up Sprint Series associated with Start Date selected --> Filter Team Cost table to only values for sprints in that Sprint Series --> Display cost for selected team over specified number of sprints

 

Where I'm stuck is getting the visual table to only show costs associated with a given sprint series as shown in the top of the message. I can get it to filter to a single team, but the visual table will show the cost associated with all dates, not just the ones in the sprint series selected.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thank lbendlin
    Hi, Bbeckert 

    Based on your description, I have created these two tables:

    Table1:

    Table2:

    Relationship:

     

    First, I created a numeric range parameter:

    I created two more slicers:

     

    I've created a new measure using the following DAX expression. This measure returns the last date of the user's selected sprint series:

    Measure =   
        VAR _spring = SELECTEDVALUE(Spring[Spring])
	VAR _team = SELECTEDVALUE(Table2[Team])
	VAR _date = SELECTEDVALUE(Table1[Dev Start Date])
	VAR _index = CALCULATE(
		SELECTEDVALUE(Table1[Sprint Series]),
		'Table1'[Dev Start Date] = _date
	)
	VAR _next_index = IF(
		_spring = 1,
		_index,
		_index + (_spring - 1) * 10
	)
	RETURN
		MAXX(
			FILTER(
				ALL('Table1'),
				'Table1'[Sprint Index] = _next_index
			),
			'Table1'[Dev Start Date]
		)

    I've created another metric to control the display of the corresponding project date:

    Measure 2 = 
        VAR _start = SELECTEDVALUE(Table2[Sprint Start])
	VAR _end = SELECTEDVALUE(Table2[Sprint End])
	VAR _spring_1_devdate = IF(
		SELECTEDVALUE(Spring[Spring]) = 1,
		SELECTEDVALUE(Table1[Dev Start Date])
	)
	RETURN
		IF(
			_end = [Measure] || _start = [Measure],
			1,
			0
		)

    Create a table visual as follows:

    Here are the results:

    When I select a sprint, and I select the corresponding team and dev date:

     

    When I choose 2 sprints:

    I've provided the PBIX file used this time below.

     

     

    Best Regards

    Jianpeng Li

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

     

     

     

4 Replies

  • Bbeckert's avatar
    Bbeckert
    New Member
    2 years ago

    For the purposes of this post, the Team Cost values are just randomly generated numbers. So the Sprint for Team 01 starting on 6/3 should show as $149,944 in both tables I pasted. Sorry for the confusion on that.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank lbendlin
    Hi, Bbeckert 

    Based on your description, I have created these two tables:

    Table1:

    Table2:

    Relationship:

     

    First, I created a numeric range parameter:

    I created two more slicers:

     

    I've created a new measure using the following DAX expression. This measure returns the last date of the user's selected sprint series:

    Measure =   
        VAR _spring = SELECTEDVALUE(Spring[Spring])
	VAR _team = SELECTEDVALUE(Table2[Team])
	VAR _date = SELECTEDVALUE(Table1[Dev Start Date])
	VAR _index = CALCULATE(
		SELECTEDVALUE(Table1[Sprint Series]),
		'Table1'[Dev Start Date] = _date
	)
	VAR _next_index = IF(
		_spring = 1,
		_index,
		_index + (_spring - 1) * 10
	)
	RETURN
		MAXX(
			FILTER(
				ALL('Table1'),
				'Table1'[Sprint Index] = _next_index
			),
			'Table1'[Dev Start Date]
		)

    I've created another metric to control the display of the corresponding project date:

    Measure 2 = 
        VAR _start = SELECTEDVALUE(Table2[Sprint Start])
	VAR _end = SELECTEDVALUE(Table2[Sprint End])
	VAR _spring_1_devdate = IF(
		SELECTEDVALUE(Spring[Spring]) = 1,
		SELECTEDVALUE(Table1[Dev Start Date])
	)
	RETURN
		IF(
			_end = [Measure] || _start = [Measure],
			1,
			0
		)

    Create a table visual as follows:

    Here are the results:

    When I select a sprint, and I select the corresponding team and dev date:

     

    When I choose 2 sprints:

    I've provided the PBIX file used this time below.

     

     

    Best Regards

    Jianpeng Li

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