AVERAGEX with Filter averaging locations selected through drop down or distance

[Anonymous]

I am working on a dashboard that will allow the user to select a location and then select the distance from that location and it will pull in all locations that fall into that radius.  I got the initial part to work, distance, finding locations.  

However, I have several measures that are simply YTD and Averages in cards on the left and I'm having trouble fixing them so they will either show the locations that are pulled in via the distance map or if the user selects some filters.  I got my other grids/maps to work by simply pulling in a field I created that states "Distance Location = 1" and that filters out all locations that aren't within the radius.  

Below is my DAX as it is currently.  I attempted to put a filter on this but I get a "Couldn't load the data for this visual, Cannot be used in computations because it does not havea ny columns."

My field is "Measures Fact[Distance Location Count].  I apologize but I can't share my PBIX file, not sure if I can strip things out of it.  

Avg per Location - Revenue YTD =
AVERAGEX(
    KEEPFILTERS(VALUES('DIMDistance'[Location])),
    CALCULATE([Distance Revenue YTD])
)

Avg per Location -  Revenue YTD =
AVERAGEX(
    KEEPFILTERS(VALUES('Distance'[Location])),
    CALCULATE([Distance Revenue YTD], FILTER('Measures Fact','Measures Fact'[Distance Location Count] = 1)
))



Distance Revenue YTD is as below:  (Use relationship is being used because I do a lot of rolling 12 month calculations and datesfilter is not linked to the main fact tables).

TOTALYTD(
		SUM('FactDistance'[REVENUE]),
		'DatesFilter'[Date], 
		USERELATIONSHIP ( 'DIMCalendar'[Date], 'DatesFilter'[Date]  )
	)

[v-frfei-msft]

Hi @Anonymous ,

Is there any relationship between DIMcalendar table and Measures Fact. I think this should be the root case of this issue. Could you please create a sample data to me if possbile? You can upload your files to One Drive and share the link here.

Regards,

Frank

[Anonymous]

Hi @v-frfei-msft 

There is a relationship but I have to use the USERELATIONSHIP because I have a rolling 12 month trend line calculation going on that uses the DatesFILTER table.  So on the dashboard the filter relates to "DATESFILTER" which means I need to use USERELATIONSHIP to get the other non-trend lines to work.  

It's a pretty big datamodel with data I can't share.  I'll see if I can dumb it down and anonymize it.  I recreated my DAX to look like the below.  I thought it was working until I tested it.  I can't seem to get it to dynamically realize when someone pushes the mileage slicer.  This only 'seems' to work if i put the values in a table and place the Miles marker in the table with it.  But I'm trying to get this to work with cards, there it returns 0.  I suspect because it doesn't know what is being triggered by the miles slicer.

Distance Avg Per Location - Revenue YTD = 

Var varDistanceMeasure = IF([Miles]<='N Miles'[N Miles Value], SUM('FactDistance'[REVENUE]), 0)
var varDistanceYTD = 
TOTALYTD(
		 ('Measures Fact'[Distance Revenue]),
		'DatesFilter'[Date], 
		USERELATIONSHIP ( 'DIMCalendar'[Date], 'DatesFilter'[Date]  )
	)

var varDistanceLocations = [Distance Comparison Location Count]  --this is populated with a 1 depending on whether or not the distance calculated between the chosen location is less than the miles selected in the slicer.  Related to the above "N_MILES" calc.
	

RETURN 

 (
	DIVIDE(varDistanceYTD, varDistanceLocations)
)

[Anonymous]

Hi @v-frfei-msft 

One thing that may help me is if I could understand why the calculation is getting a 0 in the total line for when I pull it into a table?  I get the below when I try to sum up the # of locations that fall within the radius that a user selects and I get 0 when I try to take this and put my TotalYTD in a table.  Since my cards also return 0's i was wondering if this is a clue.

[Anonymous]

@v-frfei-msft 

So I'm very close to what I need to do I just ran into one last hiccup.  I can get it to work if I pull the location count measure into a table and set the filter to LocationCount = 1.  This will allow the table to only pull in the locations that match.  Whenever I make it a card it pulls every single location and Power BI doesn't let me set the Location Count FIlter = 1.  

THe problem isn't with the dates, it's with the calculation below not being able to see what locations are 'selected' when the user utilizes the miles filter.

My code is below:

Distance     Revenue YTD  = 
var varYTD = TOTALYTD(
		 ([Distance    Revenue  ]+0),
		'DatesFilter'[Date], 
		USERELATIONSHIP ( 'DIMCalendar'[Date], 'DatesFilter'[Date]  )
	) 

var varDistanceLocations = CALCULATE(SUM('DIMComparison'[LocationCount]), FILTER('N Miles', [Miles] <= 'N Miles'[N Miles Value]))
+ 0 

RETURN
--varYTD
Divide(varYTD,varDistanceLocations)

So I thought of another way to do this in kind of a rigged sort of way.  If I could create a table and make the Total for the above measure look like a card that would allow me to accomplish what I want but just in a roundabout way.

[Anonymous · Accepted solution]

Well I figured out a way to do this and it's a lot less fancier than what i was trying.  Since I didn't have a lot of locations I created a table that had the distances pre-mapped between every location.  I then loaded this to my dataset and set things off of it.   And now it's working.