topic Re: Create a New Column in the One Side of a One-Many Relationship in DAX Commands and Tips

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

kaikai24 — Wed, 06 Nov 2024 18:18:24 GMT

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?

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

FreemanZ — Wed, 06 Nov 2024 18:37:05 GMT

hi kaikai24 ,

try like:

CountReview =

COUNTROWS(

FILTER(

RELATEDTABLE(main),

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

)

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

Anonymous — Thu, 07 Nov 2024 02:06:36 GMT

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.

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

Poojara_D12 — Thu, 07 Nov 2024 05:10:21 GMT

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.