Managing visual filtering with no active relation in calendar date

[pratafran]

Hello!

I have the following issue:

1 Database with shipments including among others, ATD (Actual Time of departure), and ATA (Actual Time of Arrival).

Those 2 dates are connected to a "Date" column in a Calendar Table being ATA the active relationship.

Now I want to put 2 bar charts, grouping the quantity of shipments Arrived (by ATA) and Dispatched (by ATD) by Year/Month and a table below with the shipment details.

The first chart has the Calendar Date (which represents the ATA because it is the active join) in X and the Shipment ID in Y (with a calculation of Distinct Count). It works fine and by selecting each month bar, I can dinamically filter in the bottom table the details.

I want to do the same with the other chart but grouped by ATD. I was succesul in doing the chart by putting the Calendar Date in X and the following Meaure in Y:

CALCULATE(

DISTINCTCOUNT(Database[ShipmentID]),

USERELATIONSHIP('Calendar'[Date],Database[ATD])

)

The chart shows fine, but the problem is that when I want to select a column, it fails to filter the details in the bottom table. And it makes sense, since it is a meaure which result is a number but with no agregation of shipments IDs within it.

The question is, do I have any alternative to reach my objective?

Thanks in advance!

[pratafran]

well, by adding the measure to the table makes it works!.

The thing is that my problem description of 2 visuals 1 table was just a simplification of the real problem. I actually have at least 4 different Date events connected to the calendar table which I want to show as descripted in different bar charts, but same table details.

For making it work that way, I have to add each measure to the table, resulting in blank lines that I dont want when filtering.

For instance:

Selecting december's 6 shipments by ETD, results in showing those 6 shipments + some more lines that has the ETA in december too.

[V-lianl-msft]

Hi @pratafran ,

Based on your description, you want to hide blank rows based on bar chart filtering.

I'm not clear about your data model. You can try to create a measure to apply to the visual level filter of the table visual using the if statement. The measure may be like this:

IF(NOT(ISBLANK(XXXXX by ETD)),1)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pratafran]

Thanks. I'm not sure if I understood the proposed solution but I was not able to solve the issue.

For doing it easier, I created an example model showing the problem. 

Model 

The first chart is the simplest. Counting quantity of shipments by ATA (Actual time of arrival) in the last 6 months. This is created using the active relation between ATA column in Shipments table and Date column in Calendar table so when clicking on any month, it correctly filters the table with the corresponding shipments.

For the second and third chart (by ETA and by ETD dates), I created 2 measures (Qty by ETA and Qty by ETD), counting the shipments but forcing the relations between those dates and the Date column in calendar table.

That way, the charts show the shipments grouped in the corresponding month (by Estimated time of arrival and by Estimated time of departure).

In order to get the same result when clicking those charts (filtering in the table the corresponding shipments), I had to add both measures to the table (something I would like to avoid because I prefer not showing those columns but I think there is no option). The problem is: When I filter December in transit shipments from the second chart, the table shows 2 results, the in transit shipment expected to arrive in December (ETA) but also another in transit shipment (expected to arrive in January) but which was expected to depart in December (ETD). I understand this happens because of the Qty by ETD measure that is counting it in December but that is not the expected behavior that should be the same than chart 1.

Hope this helps to clarify the problem.

Thanks

[amitchandak]

@pratafran , does the bottom table also have measures that is using userelation?

Same date join is required. Also, check dates do not have timestamps(check by changing the format).

if so create a date like this and join

Date = [ATD].date
or
Date = date(year([ATD]),month([ATD]),day([ATD]))

[pratafran]

wow!, thanks for you increadible fast response.

To simplify, the bottom table just has Shipments IDs and a few columns of description (Name, Type, etc). It is not including data from Calendar table, just from the Main Database.

It is not including measures, just attributes.

Dates are in "Date" format, no "Date/Time".

The Calendar Table was created with the DAX "CALENDARAUTO()". Not sure if that can cause a problem.

Your DAX suggestion is for removing the timestamps?. I forced the Date format from PowerQuery when importing the Data source.