Create a New Column in the One Side of a One-Many Relationship

[kaikai24]

Hello everyone,

I have a one-to-many relationship between the 'Main' table and the 'LastSixMonthCount' table on the column 'Classification'. The 'Main' table has all the customer reviews. Now I am trying to create a new column in the 'LastSixMonthCount' table, which calculates the counts of customer reviews for each classification in the last six months. My DAX is below, but it does not work. Does anyone have any ideas?

[Poojara_D12]

Hey @kaikai24 

Please try using this DAX:-

LastSixMonthReviewCount = 
VAR SixMonthsAgo = TODAY() - 180
RETURN
    CALCULATE(
        COUNTROWS('Main'),
        'Main'[Classification] = 'LastSixMonthCount'[Classification],
        'Main'[ReviewDate] >= SixMonthsAgo
    )

Please let me know if it works for you.

Regards,

Poojara.

[Anonymous]

Hi @kaikai24 ,

I create two tables as you mentioned and I also create a relationship between them.

Then I create a new calculated column and here is the DAX code.

CountReview = 
CALCULATE (
    COUNT ( 'Main'[DA ReviewId] ),
    FILTER (
        'Main',
        AND (
            'Main'[Classification] = EARLIER ( 'LastSixMonthCount'[Classification] ),
            DATEDIFF ( 'Main'[Date of latest rating], TODAY (), MONTH ) <= 6
        )
    )
)

Best Regards

Yilong Zhou

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

[FreemanZ · Accepted solution]

hi @kaikai24 ,

try like:

CountReview =

COUNTROWS(

    FILTER(

        RELATEDTABLE(main),

        main[datelast] >= EDATE(TODAY(), -6)

    )

)