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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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