power bi dax Min date of each category

[peternznguyen]

Hi Community,

I am familiar with SQL and I can write a query to return results of a query to Select MIN(Date), MAX(Date), SUM(quality) and GROUP BY. However, I am new to Power BI and DAX and find it difficult to do the same on Power BI. Below is my situation.

These tables on Power BI:

Dim_ManefactureDate

Dim_ReleaseDate

Fact_OrderID

Table Relationships 

Adding a table visualization to a new page to show data from three tables above, data is showing as below: 

Under Values of Visualizations, when selecting SUM over Netweight, it automatically summarizes expected Netweight. However, for ManufactureDate and ReleaseDate, when selecting Earliest then Power BI table shows unexpected 1/01/1900 values like this:

I expect earliest date of each OrderID as below:

I have also tried to use a DAX function to create a new column but it gets error

ManufactureDate_Earliest = 
    VAR Sum_Netweight = SUM(Fact_OrderID[NetWeight])
    VAR GroupBy_OrderID = GROUPBY(Fact_OrderID,Fact_OrderID[OrderID])
    RETURN
        CALCULATE(
             MIN(RELATED(Dim_ManufactureDate[DateBK]))
            )

Thank you very much for your help

Peter

[SpartaBI · Accepted solution]

@peternznguyen there are few ways to approach it, but becasue you are very new, so let's try this. Why didn't you just put the date columns from the fact instead of the dim?

[SpartaBI · Accepted solution]

@peternznguyen there are few ways to approach it, but becasue you are very new, so let's try this. Why didn't you just put the date columns from the fact instead of the dim?

[peternznguyen]

@SpartaBI , thank you for your reply. Dim_ManufactureDate and Dim_ReleaseDate were from Dim_Date that contains many other columns such as DayOfMonthCode, DayOfWeekCode, FullDateDesicription, MonthOfYearDescription, etc., so that's why I don't put everything in the Fact table. I have tried using Matrix but I don't expect the result from Matrix.

[SpartaBI]

@peternznguyen ok, will PM you now

[SpartaBI · Accepted solution]

@peternznguyen  the measures will look like:

Earliest_CompletionDate = 
CALCULATE(
    MIN(CompletionDate[DateBK]),
    CROSSFILTER(Fact_ShippingKPI[CompletionDate_DateSK], CompletionDate[DateSK], BOTH)
)

      

Showcase Report – Contoso By SpartaBI