Are table relationships or filters the source of all issues?

[ajdm2007]

Hello everyone,

I am working a new project in Power BI, and for this occasion, I am using tables and relationships, such as the calendar table and one for measures, as well as other tables specific to the type of story I want to tell.

During the development of the visualizations, I have encountered various issues, some of which I’ve been able to resolve, while others remain unsolved.

The source data table is a help desk tickets table. It contains several fields that are associated or related to other tables, but I believe the relationship between this table and the calendar table is what’s causing all the issues.

tickets has several date-type fields: date_entered, which refers to the date the ticket was registered in the system; date_responded_utc, which refers to the date the ticket was responded to; and finally, the date_resolved field, which refers to the date the ticket was resolved.

Additionally, since these are datetime fields, I created another calculated column called date_only, which stores only the date from the date_entered field. I also have another calculated column called date_for_analisys, which is the result of the following formula:

COALESCE(tickets[date_resolved], TODAY())

In the relationship diagram, tickets is related to calendar using the date_only field. In the report filters, I am using the date_for_analisys field.

Filters

Given that the visualizations are not working as expected, I’m wondering: Is the problem with the relationship or the filter?

When I change the relationship between the tables, and instead of using date_only, I use date_for_analisys, all the visualizations stop working.

Any ideas?

Thank you,

[MNedix]

Heya,

I may understand your problem since I have a similar scenario where the entities have multiple dates associated with them (e.g. Created date, stage change date, updated etc.) The way I approached it, was to first clean up the dates (I didn't need the time); I used either DATEVALUE or split the column by delimiter in Power query. Then I connected all these date columns with the Date table (one active and the rest inactive). Then, whenever I designed any measure which was using any of these dates I "activated" the inactive relationship using USERELATIONSHIP in the calculation. For example, if I wanted to know the number of items closed this month I would do something like this:

Closed MTD = CALCULATE(DISTINCTCOUNT([ProjectID]),DATESMTD('Date'[Date]),USERELATIONSHIP('Date'[Date],'Table'[Closed date]))

I hope it makes sense.

Cheers,

[ajdm2007]

Hello @MNedix 

I knew I wasn’t the only one having this problem. Thank you very much for taking the time to respond.

If I understand correctly, you are basically suggesting to keep the relationship between tickets -- date_only and calendar -- date and create a new one between tickets -- date_analisys and calendar -- date, but leave the latter INACTIVE, only activating it within the DAX formula when I need to use that date instead of the permanently active relationship.

Is that correct?

Thank you again, 

[MNedix · Accepted solution]

Heya, Yes, pretty much. This is how my Date table looks like in one of the files I have:

[ajdm2007]

Hi @MNedix ,

I will take a look on my model.

Thank you so much.

[HotChilli]

"visualizations are not working as expected" - More details please.

[ajdm2007]

Hello @HotChilli,

Filtering by date_for_analisys and the relation ship is tickets --> date_only with calendar --> date

Filtering by date_for_analisys and the relation ship is tickets --> date_for_analisys with calendar --> date

Hopefully, this make sense for you now.

Thank you,