Forum Discussion
Filter visual based on an inactive relationship
I have a number of measures that take inactive relationships into account when it comes to dates. I have one "Calendar" table that is linked to many date columns, inactively, and the measures just calculate according to the relationship I've pointed them at in order to have a date slicer. It works great.
But how do I make this date slicer work when I don't actually need a measure?
I have a table that has rows showing a particular assessment an employee has given to a volunteer, across different skill categories, and on what date they made the assessment.
My aim is to have a pivot table that has the employee on the row labels, the skill categories for the columns, and a count of the assessments as the values. It will do this without any measures involved, just the raw columns.
BUT I only want it to count the values that fall inside the date slicer. In short, I want it to take the inactive date relationship into account before it pivots.
Is there a way to do this?
- are you able to share your model or some dummy data.
you are doing a count therefore you are using a measure, you need to calculate that count with the userelationship() function which will force the inactive relationship to be counted with the correct date.
this is a good article that goes through a few scenarios
https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
5 Replies
- vanessafvg
Community Champion
are you able to share your model or some dummy data.
you are doing a count therefore you are using a measure, you need to calculate that count with the userelationship() function which will force the inactive relationship to be counted with the correct date.
this is a good article that goes through a few scenarios
https://www.sqlbi.com/articles/userelationship-in-calculated-columns/ - amitchandak
Super User
Hi abcd
If you don't want to use measure, I think you need to build relationships between your calendar table and other tables.
Due to I don't know your data model, I build sample tables to have a test.
Calendar Table:
Calendar = CALENDAR(DATE(2020,01,01),DATE(2020,02,29))Build relationships between date columns in two tables.
You want a Pivot table, I think build a Matrix can achieve your goal. You need to Add Employee and Category in Rows and Assessment in Values. You need to turn off Step layout in Row headers.
Result is as below.
Default:
Show Assessments between 2020/1/1 and 2020/1/10.
For more info to Relationships and Matrix:
Model relationships in Power BI Desktop
Create matrix visualizations in Power BI
If this reply still couldn't help you please show me more details about your data model and the result you want.
Or you can provide me with your pbix file by your OneDrive for Business.
You can download the pbix file from this link: Filter visual based on an inactive relationship
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi abcd
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
Disclaimer: I know this isn't an efficient solution and it isn't feasible with large datasets, but I couldn't come up with anything better 🤔
abcd, I was able to avoid this ambiguity issue by just making a duplicate query of my target table (e.g. "employeeAssements" in your example)... then I can have an active relationship with the duplicate without worrying about ambiguity
