Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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  @Anonymous ,

 

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  @Anonymous ,

 

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
Anonymous
Not applicable

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  @Anonymous ,

 

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!

 

Anonymous
Not applicable

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 @Anonymous ,

 

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!

 

Anonymous
Not applicable

sample data files attached.

Test Data Zip File 

Hi @Anonymous ,

 

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!

 

Anonymous
Not applicable

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  @Anonymous ,

 

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!

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.