Histogram Filtered by Date when using Calendar Table and USERELATIONSHIP - Confused

[paulj1]

Hello Community...  Bumped..... Still confused !

I hit a snag today that has confused me...

So i have a table which many thousands of records vData[area] , indexed by measured date vData[date] , however this date field is an inactive relationship to the Calander table as the active date relationship is elsewhere in the data model.

On my report the date filter is from the Calendar[Date] field, as i want single date filter to operate on different parts of the model.

So for example if i want the MAX, MIX, MEDIAN etc its easy, the measure is below and it works utilising the inactive relationship;

Median Area = CALCULATE( MEDIAN( vData[area] ), USERELATIONSHIP( 'Calendar'[Date] , vData[date] ))

Where I hit a problem was I want to do a histogram of all areas, it doesnt work, if I add vData[area] to a bar chart its Blank, why because the date filter on the report is operating else where in the model. what is a way to get a list of individual values for area, that I can chart using the inactive date relationship like I do with USERELATIONSHIP in the other measures.....

I suspect there is a DAX solution... help......

I was asked for an example file....  so ive made an example https://1drv.ms/u/s!ApcyZzyFx2-9hZQq06gm9SM7JwLpLA?e=T3js7x

To explain the example file...

1 Production process lasting a number of days.

2. Need a single date filter, that when a day is chosen, the report shows the activity at each step on that day.

3. Calander has relationship with Step 1 table, use measures and USERELATIONSHIP for other steps to make it work.

4. The Histogram doesnt work, in snip below want histogram to show the 67 measurements on that day, not the 105 that went into work that day.

[paulj1]

Anyone fancy a challenge, this is a interesting one ???

[AnkitKukreja]

Hi @paulj1 

It would be very easy to help you out if you can share your pbix file without pii or share your sample data in excel and let me know what is the outcome you're expecting out of it.

Thanks,
Ankit Kukreja

LinkedIn