Crossfiltering / Measure / Many-to-many with a bridge table

Question

Dear Members of the Power BI CommunityI am stock with the following case, hopefully somebody can give me a hint.&nbsp;Initial situationI have a dimCalender table and a second table with events "Eventtable". The goal is to filter the Eventtable respectively the time period by the date. So far so easy, each event has a start and end date. To avoid a many to many relationsship, I created a bridge table as a calculated table with the DAX-Code below:HTBL_Events = FILTER(CROSSJOIN(DFL_dimDate,Eventtable),Eventtable[Start date] &lt;= [Date] &amp;&amp; Eventtable[End date] &gt;= [Date])&nbsp;This solution allows me to filter every single day. As a result I can see if an Event happens at this day or not. With this bridge table in use the following relationsships resulting:&nbsp;dimDatetable 1:m Bridge table: There is one date field and several dates in the bridge table (reason: because of the duration of one event ID over several days the ID can have more than one date which is concerned)Eventtable 1:m Bridge table: There is one ID as a primary key in Eventtable and more than one in the bridge table (reason: the event can have a duration of more than one day)&nbsp;Actual QuestionWith the above decribed situation the problem arises that I am not able to filter the table Eventtable by the dimDatetable because of the dircation of the relationsship. So far so god, because of other tables and the thousands of recommendations I am searching for a proper solution to filter the table. My intended solution is a measure which activates the relationsship with the crossfilter function. But I am not able to generate the table with the columns: Start date / End date / Event title which are filtered over the dimTable with the support of the created bridge table.All trials so far ended in the message that a measure is not able to show several values. But out of my actual understanding I have the feeling that this is more out of a lack of skills from my side than from dax and the possibilities from DAX.&nbsp;&nbsp;&nbsp;I hope the discription is more or less clear, if you have any questions do not hesitate to comment. 😉Thanks for your support and tippsMike

amitchandak · Answer

Anonymous , the Bridge table should be on 1 side not many.
&nbsp;
What you need here? See if the blog or attached file can help
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785
&nbsp;
&nbsp;

johnt75 · Answer

Set the relationship between the bridge table and the event table to be bidirectional. That way the filters will flow from the date table to the event table but they will not flow in the other direction as the relationship from date to bridge remains single directional

anonymous · Answer

Hi johnt75Unfortunately not possible because of circular dependencies with another table. Like I already mentioned there must be a solution with the function crossfilter or a function with a similar behaviour.Best regardsMike

anonymous · Answer

Hi amitchandakUnfortunately this is not what I am looking for. The goal is visual table with the values "Start Date", "End Date" and "Title". What I am looking for is a possibility to switch the filtering from m:1 to a bidirectional relationship. The examples you attached in your comment have the target to calculate the amount of days. My goal is to have the dates and the title filtered by the dimTable.Best regardsMichael

Forum Discussion

Crossfiltering / Measure / Many-to-many with a bridge table

4 Replies

Related content

Many to Many Measures

Understanding many to many relationship

many to many

Behaviour of many to many relationships

Dax with Many to Many