Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
szielinski
Frequent Visitor

Row Values Blank with relationship

Hello, have a issue with a dax and a relationship with  calendar table, one of my dax works perfectly but the other dont, and dont find any reason with shouldn't, i make sure the date relationship are set by data field type etc, i need that my 2 formulas work in a table visual with the calendar date field and the dax in the other table conected thru the date relashionship 

 

formula one works = 

CALCULATE(
    [Customers],
    FILTER(
        ALL(Sales),
        Sales[OrderDate] <= MINX(FILTER(Sales, [CustomerKey] = EARLIER(Sales[CustomerKey])), Sales[OrderDate]) + 90
        && Sales[OrderDate] > MINX(FILTER(Sales, [CustomerKey] = EARLIER(Sales[CustomerKey])), Sales[OrderDate])
    )
)
 
Formula 2 dont work :
VAR MinDateFirstPurchase = CALCULATE(MIN('Customers'[DateFirstPurchase]), VALUES(Customers[AltCustomerKey]))
VAR ThreeMonthsLater = EOMONTH(MinDateFirstPurchase, 3)
VAR OneMonthLater = EOMONTH(MinDateFirstPurchase, 1)
VAR FirstDayOfMonthOneMonthLater = DATE(YEAR(OneMonthLater), MONTH(OneMonthLater), 1)
RETURN
CALCULATE(
[Customers],
FILTER('Calendar', 'Calendar'[Date] <= ThreeMonthsLater && 'Calendar'[Date] >= FirstDayOfMonthOneMonthLater)
)
 
szielinski_0-1699240435310.png

 

1 REPLY 1
123abc
Community Champion
Community Champion

It appears that you are having trouble with the second DAX formula. The issue might be related to the use of filters and relationships. Let's break down your second DAX formula to understand it better and identify potential issues:

 

VAR MinDateFirstPurchase = CALCULATE(MIN('Customers'[DateFirstPurchase]), VALUES(Customers[AltCustomerKey]))
VAR ThreeMonthsLater = EOMONTH(MinDateFirstPurchase, 3)
VAR OneMonthLater = EOMONTH(MinDateFirstPurchase, 1)
VAR FirstDayOfMonthOneMonthLater = DATE(YEAR(OneMonthLater), MONTH(OneMonthLater), 1)
RETURN
CALCULATE(
[Customers],
FILTER('Calendar', 'Calendar'[Date] <= ThreeMonthsLater && 'Calendar'[Date] >= FirstDayOfMonthOneMonthLater)
)

 

Here are some potential issues and troubleshooting steps:

  1. Check Relationships: Make sure that the relationship between the 'Calendar' table and the 'Customers' table is correctly established. The relationship should be based on the date fields, and it should be a one-to-many or many-to-one relationship, depending on your data model.

  2. Check Data Types: Ensure that the data types of the date fields used in the relationship are consistent. The data type for the date column in the 'Calendar' table should match the data type of the date column in the 'Customers' table.

  3. Check Data Availability: Ensure that there are matching dates in both the 'Calendar' table and the 'Customers' table for the period you are trying to filter. In your DAX formula, you are filtering based on dates, so there must be corresponding dates in the 'Calendar' table for the filter to work.

  4. Debugging DAX: To debug the DAX formula, you can break it down step by step. First, try evaluating the variables (MinDateFirstPurchase, ThreeMonthsLater, OneMonthLater, and FirstDayOfMonthOneMonthLater) individually to see if they are producing the expected results. You can use DAX tools like DAX Studio to help with this.

  5. Test with Visuals: Create visuals to help you understand what is happening. For example, you can create a table visual with date columns from both the 'Customers' and 'Calendar' tables to see if the relationship is working as expected.

  6. Error Messages: Check for any error messages or warnings that might be generated when you use the DAX formula. These messages can provide valuable insights into what might be going wrong.

By following these steps, you should be able to identify the issue with your second DAX formula and make the necessary adjustments to ensure it works as expected. If you encounter specific error messages or have more information about the issue, please provide that information for more targeted assistance.

 

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

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors