cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kevindmonte
Helper II
Helper II

Get unfiltered values from One Side of relationship table while keeping week ending filter.

Hi, 

 

I am really struggling to get this working. I have two tables with one to many relationship. I want to plot a line on the combo chart using SUM of a column from the One Side of table in the relationship. The filter direction is from One to Many. When I add the column from one-side table, I get the total SUM plotted across X-Axis. What I want is the Line to show cumulative values (SUM) for each Week Ending Friday Date (for example 29 from the Availability table for each week ending irrespective of the data ). If I use bi-directional filtering then I think I am getting close but the line then shows varying figures like 26, 24., 29 etc. I have attached the sample data and PBIX below. In the report, I have added two pages; one page with default one-way relationship and second with bi-directional relationship established via CROSSFILTER function. I have tried to show what's happening in the chart screenshot below. The Yellow constant line is the output I want to show. The pruple line is how it's getting plotted now if I use bi-directional filtering. 

kevindmonte_0-1620725929555.png

 




Would appreciate if anyone can advise the way around. (Update: I have attached the sample files in the post below:)

2 ACCEPTED SOLUTIONS

Hi  @kevindmonte ,

 

Create a measure as below:

Measure = 
IF(ISFILTERED('Availbility'[Resource Name]),CALCULATE(SUM('Availbility'[Available FTE per Week]),FILTER(ALL(Availbility),'Availbility'[Resource Name]=SELECTEDVALUE(Availbility[Resource Name])&&'Availbility'[W.E.Fri]=MAX('Availbility'[W.E.Fri]))),CALCULATE(SUM('Availbility'[Available FTE per Week]),FILTER(ALL(Availbility),'Availbility'[W.E.Fri]=MAX('Availbility'[W.E.Fri]))))

And you will see:

v-kelly-msft_0-1620954391628.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

Hi  @kevindmonte ,

 

You need to modify part of your measure :

CALCULATE (
SUM ( 'Availbility'[Available FTE per Week] ),
FILTER (
ALL ( Availbility ),
'Availbility'[W.E.Fri] = MAX ( 'Availbility'[W.E.Fri] )
)
)

 as below: 

CALCULATE (
SUM ( 'Availbility'[Available FTE per Week] ),
FILTER (
ALLSELECTED ( Availbility ),
'Availbility'[W.E.Fri] = MAX ( 'Availbility'[W.E.Fri] )
)
)

If you use "All",it means to remove all the filters on the field,but "Allselected "is the opposite,it will reflect the filter you made on the field.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

View solution in original post

9 REPLIES 9
kevindmonte
Helper II
Helper II

apologies, no luck with formatting the code in my answer above but here's the screenshot on the final code. 

kevindmonte_0-1620994576944.png

 

Hi  @kevindmonte ,

 

You need to modify part of your measure :

CALCULATE (
SUM ( 'Availbility'[Available FTE per Week] ),
FILTER (
ALL ( Availbility ),
'Availbility'[W.E.Fri] = MAX ( 'Availbility'[W.E.Fri] )
)
)

 as below: 

CALCULATE (
SUM ( 'Availbility'[Available FTE per Week] ),
FILTER (
ALLSELECTED ( Availbility ),
'Availbility'[W.E.Fri] = MAX ( 'Availbility'[W.E.Fri] )
)
)

If you use "All",it means to remove all the filters on the field,but "Allselected "is the opposite,it will reflect the filter you made on the field.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

View solution in original post

kevindmonte
Helper II
Helper II

Can anyone advise what formatting option they use when adding DAX code to the question. I tried different formats but when posting I get this error "Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied." . It wont let me submit the changes at all. 

Hi @kevindmonte ,

 

Click the button below to clear the existing format :

v-kelly-msft_0-1620987358874.png

Refresh the page then paste the code again.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

kevindmonte
Helper II
Helper II

sample data files attached.

Test Data Zip File 

Hi @kevindmonte ,

 

Can you advise me the calculation logic to get the result of 29?


Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

Hi @v-kelly-msft so basically 29 in this case is the total count of all people available on a given Week Ending Friday from the Dimension table(availability). I have been told that that value will be same across all weeks and hence they want to plot that value as a line across the the Y-Axis (spread over week ending Fridays's shown on X-axis). When no resource name is selected in the slicer then the line will show 29. When one or more resource names are selected then it needs to show the sum of the availaility for these selected resources. Hope this makes sense?  (I think I managed to get the latter part but when there is no resource selected, the the line goes zig-zag possibly due to cross-filtering).  

Hi  @kevindmonte ,

 

Create a measure as below:

Measure = 
IF(ISFILTERED('Availbility'[Resource Name]),CALCULATE(SUM('Availbility'[Available FTE per Week]),FILTER(ALL(Availbility),'Availbility'[Resource Name]=SELECTEDVALUE(Availbility[Resource Name])&&'Availbility'[W.E.Fri]=MAX('Availbility'[W.E.Fri]))),CALCULATE(SUM('Availbility'[Available FTE per Week]),FILTER(ALL(Availbility),'Availbility'[W.E.Fri]=MAX('Availbility'[W.E.Fri]))))

And you will see:

v-kelly-msft_0-1620954391628.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

Hi @v-kelly-msft that did the trick. The only thing I had to do was the measure was still returning 29 when I added Resource Name slicer and selected a few resources. To handle that I changed the code a bit to handle the selected values and if there were more that on values selected. Below is the changed code. Many thanks for your help on this. This has been a pain for me to get my head around it.


Kelly Measure 2 =
IF (
ISFILTERED ( 'Availbility'[Resource Name] ),
IF (
// I modified to add below condition to check if user has selected one or more resource values and then return the availability values accordingly.
HASONEVALUE ( Availbility[Resource Name] ),
CALCULATE (
SUM ( 'Availbility'[Available FTE per Week] ),
FILTER (
ALLSELECTED ( Availbility ),
'Availbility'[Resource Name] = SELECTEDVALUE ( Availbility[Resource Name] )
&& 'Availbility'[W.E.Fri] = MAX ( 'Availbility'[W.E.Fri] )
)
),
CALCULATE (
SUM ( 'Availbility'[Available FTE per Week] ),
FILTER (
ALLSELECTED ( Availbility ),
'Availbility'[Resource Name]
IN VALUES ( Availbility[Resource Name] )
&& 'Availbility'[W.E.Fri] = MAX ( 'Availbility'[W.E.Fri] )
)
)
), --Back to Kelly's code.
CALCULATE (
SUM ( 'Availbility'[Available FTE per Week] ),
FILTER (
ALL ( Availbility ),
'Availbility'[W.E.Fri] = MAX ( 'Availbility'[W.E.Fri] )
)
)
)

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors