DAX - How to work out USERELATIONSHIP and Aggregate function (first) together

[emilypoon]

Hi,

I have a dataset with the following columns.

- PolCountryName = display the country name from my dataset

- First PolCountryName = display the "first" PolCountryName using the aggregated function

- Year1 = display the "ShipmentItemManifested" column in year

- Year2 = display the "MasterDate" column in year

- Year3 = display the "LandsideBookingReceiptDate" column in year

- Percentage = display the DAX calculation using the below formula:

Percentage =

CALCULATE(AVERAGE(OrderedItemSummaryBI[Table_UtilizedRatePerContainer]),

USERELATIONSHIP(OrderedItemSummaryBI[DateOnly_ShipmentItemManifested],MasterCalendar[MasterDate]))

As per above setup, I want to activate the relationship between ShipmentItemManifested & MasterDate.

According to my model, the current active relationship is between LandsideBookingReceiptDate & MasterDate, but not between ShipmentItemManifested & MasterDate.

When I calculate the Percentage, I want to activate the relationship between ShipmentItemManifested & MasterDate and that's why I calculated using the above formula. The calculation is correct and successful but the problem is that the "First PolCountryName" column becomes blank if I have a value in the "Percentage" column.

How can I ensure the "First PolCountryName" column is not blank given that I must have USERELATIONSHIP in my model? In addition, I must stick with the "First PolCountryName" column for some reason and I can't just simply use "PolCountryName".

If I simply calculate the Percentage without using USERELATIONSHIP, the "First PolCountryName" column displays correctly (See below demo example).

Is there anyone can help the above problem?

Thanks!

Best regards,

Emily

[v-yulgu-msft]

Hi @emilypoon ,

If you activate the relationship between ShipmentItemManifested & MasterDate via using USERELATIONSHIP, it would make the current active relationship to be inactive, which might cause the blank value of First PolCountryNam column.

Best regards,

Yuliana Gu

[emilypoon]

@v-yulgu-msft 

In my case, I must activate the relationship between ShipmentItemManifested & MasterDate via using USERELATIONSHIP but same time I want the Percentage to be displayed properly without showing blank in the table.

What I don't understand is why the table shows blank in the FirstPolCountry even if I activate ShipmentItemManifested & MasterDate.

Any solutions to solve this problem? Thanks!