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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
paulj1
Helper II
Helper II

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

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.

 

 

Capture.JPG

 

 
2 REPLIES 2
paulj1
Helper II
Helper II

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

AnkitKukreja
Super User
Super User

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 

If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.
Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.