Calculate MAX date based on the multiple columns

[Saxon10]

Data:

I have a two tables are data and report

In data table contain item, country, date, time, area code and sales code

Country column contains INDIA, PAK, US, UK, AUS, SRI, CAND, NEWL, XXX Area code columns contain A and B Sale code column has “DMK” Report Table:

In report table contains item and sales code. (item and sales code has duplicated) Relationship (Data and Report): In both tables  Item and sales code column has common Criteria: Item and sales code Filter in Data Table: Country {India, US, UK, NEWL, XXX} and Area Code is {A,B}

Priority/order/Condition: Sometime in data table the same item has two different area code which is A and B, in this scenario the max date need to be return according to the area code “A”. If same item in data table does not contain A then return max date according to the B. Condition 1 : Area code A is first priority. Condition 2 : Area code B is second priority. Condition 1 Rule : If items is exist in data table then return max date based on the criteria, time, filter by country {India, US, UK, NEWL and area code is {A} If item has area code is "A" then, the country must be greater than or equal to 4 rows {India, US, UK, NEWL Condition 2 Rule : If items is exist in data table then return max date based on the criteria, time, filter by country {XXX} and area code is {B} If item has area code is "B" then the country equal to 1 row {XXX} If item not exist in date table then return “NA”.

Desired Result snapshot:

Herewith attached the PBI file for your reference.

https://www.dropbox.com/s/5f4u1tlq1rr2ruz/saxon10.pbix?dl=0

[Jihwan_Kim]

Hi, @Saxon10 

Please change the data type of DATE column in the DATA table, and try the below Calcualted Column in the Report table.

Max Date new column =
VAR countriescondition =
INTERSECT (
SUMMARIZE ( FILTER ( Data, Data[ITEM] = Report[ITEM] ), Data[COUNTRY] ),
{ "INDIA", "US", "UK", "NEWL" }
)
VAR countriescondition2 =
INTERSECT (
SUMMARIZE ( FILTER ( Data, Data[ITEM] = Report[ITEM] ), Data[COUNTRY] ),
{ "XXX" }
)
VAR newtable =
FILTER (
Data,
Data[ITEM] = Report[ITEM]
&& DATA[COUNTRY]
IN { "INDIA", "US", "UK", "NEWL" }
&& Data[AREA CODE] = "A"
)
VAR newtable2 =
FILTER (
Data,
Data[ITEM] = Report[ITEM]
&& DATA[COUNTRY] = "XXX"
&& Data[AREA CODE] = "B"
)
RETURN
IF (
COUNTROWS ( countriescondition ) = 4,
MAXX ( newtable, Data[DATE] ),
IF (
COUNTROWS ( countriescondition2 ) = 1
&& COUNTROWS (
SUMMARIZE ( FILTER ( Data, Data[ITEM] = Report[ITEM] ), Data[COUNTRY] )
) = 1,
MAXX ( newtable2, DATA[DATE] ),
BLANK ()
)
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Saxon10]

Hi,

Thanks you so much for your help and reply. It's working well but still there is some issue.

If same item has different sales code in report table then the current DAX giving wrong result but actual result is blanks. Please refer the snapshot.

How can I added sales code range in your exsiting DAX. The sales code column is common in both tables.

I am try to added sales code in your exsiting DAX but it giving error. 

[Jihwan_Kim · Accepted solution]

Hi, @Saxon10 

Please kindly check the below which is for the Calculated Column.

I am not quite sure if the below is the most optimized way to write, but I tried to come up with the solution.

Max Date new column =

VAR countriescondition =

INTERSECT (

SUMMARIZE ( FILTER ( Data, Data[ITEM] = Report[ITEM] && DATA[SALES CODE] = REPORT[SALES CODE] ), Data[COUNTRY] ),

{ "INDIA", "US", "UK", "NEWL" }

)

VAR countriescondition2 =

INTERSECT (

SUMMARIZE ( FILTER ( Data, Data[ITEM] = Report[ITEM] && DATA[SALES CODE] = REPORT[SALES CODE] ), Data[COUNTRY] ),

{ "XXX" }

)

VAR newtable =

FILTER (

Data,

Data[ITEM] = Report[ITEM] && DATA[SALES CODE] = REPORT[SALES CODE]

&& DATA[COUNTRY]

IN { "INDIA", "US", "UK", "NEWL" }

&& Data[AREA CODE] = "A"

)

VAR newtable2 =

FILTER (

Data,

Data[ITEM] = Report[ITEM] && DATA[SALES CODE] = REPORT[SALES CODE]

&& DATA[COUNTRY] = "XXX"

&& Data[AREA CODE] = "B"

)

RETURN

IF (

COUNTROWS ( countriescondition ) = 4,

MAXX ( newtable, Data[DATE] ),

IF (

COUNTROWS ( countriescondition2 ) = 1

&& COUNTROWS (

SUMMARIZE ( FILTER ( Data, Data[ITEM] = Report[ITEM] && DATA[SALES CODE] = REPORT[SALES CODE] ), Data[COUNTRY] )

) = 1,

MAXX ( newtable2, DATA[DATE] ),

BLANK ()

)

)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Saxon10]

Hi,

Thanks for your reply and help. It's working perfectly without any problems.

[Saxon10]

I would like to achieve my desired result by using New Calculated column option. Can you please advise.