Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

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

 

 

Need Help

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous ,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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!