topic Re: Need to create Date column with relationship table in DAX Commands and Tips

Need to create Date column with relationship table

arvindarvind24 — Wed, 21 Dec 2022 13:48:23 GMT

HI All,

Need your help on creating the date column with relationship table.

OutPut:

Code	Sale Date	icode	Grc Date
1	07-01-2022	AK001	01-01-2022
1	11-01-2022	AK002	05-01-2022
1	11-01-2022	AK003	05-01-2022
2	19-01-2022	AK004	13-01-2022
2	19-01-2022	AK005	13-01-2022
2	21-01-2022	AK001	15-01-2022
3	24-01-2022	AK002	18-01-2022
5	24-01-2022	AK003	18-01-2022
7	03-02-2022	AK004	28-01-2022
2	03-02-2022	AK004	28-01-2022
6	26-01-2022	AK001	20-01-2022
3	21-01-2022	AK002	15-01-2022
8	24-01-2022	AK003	18-01-2022
8	24-01-2022	AK004	18-01-2022
3	03-02-2022	AK004	28-01-2022

Link: https://drive.google.com/file/d/195zv-UmlYc3sU30cJ7NOf_apuyEMc0m6/view?usp=sharing

@amitchauhan @bvanderwatt @ray_ux @tamerj1 @SpartaBI @v-chenyue-msft @mangaus1111

v-jianhe-msft

Re: Need to create Date column with relationship table

Mahesh0016 — Thu, 22 Dec 2022 08:00:51 GMT

arvindarvind24 which table reference to create date column.

Re: Need to create Date column with relationship table

arvindarvind24 — Fri, 23 Dec 2022 04:16:08 GMT

HI Mahesh0016

From GRC to Sales Table.

Re: Need to create Date column with relationship table

Mahesh0016 — Fri, 23 Dec 2022 04:22:45 GMT

Hello arvindarvind24 ,

Good Morning

please can you share pbi ZIP file

Re: Need to create Date column with relationship table

Mahesh0016 — Fri, 23 Dec 2022 04:51:06 GMT

arvindarvind24

>> Create New Column.

date =lookupvalue(Grc[Grc Date],Grc[Code],Sales[Code])

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

Re: Need to create Date column with relationship table

arvindarvind24 — Fri, 23 Dec 2022 07:08:45 GMT

FYI.

Link: https://drive.google.com/file/d/195zv-UmlYc3sU30cJ7NOf_apuyEMc0m6/view?usp=sharing

Re: Need to create Date column with relationship table

arvindarvind24 — Fri, 23 Dec 2022 07:11:08 GMT

Hi Mahesh0016

LookupValue work which has direct relationship with each other.

Re: Need to create Date column with relationship table

Mahesh0016 — Fri, 23 Dec 2022 08:16:17 GMT

GRC Date = CALCULATE(

MIN(GRC[date]),

FILTER(GRC,GRC[code]=Sales[Code]))

arvindarvind24 can you improve this .

Re: Need to create Date column with relationship table

Mahesh0016 — Fri, 23 Dec 2022 10:04:29 GMT

arvindarvind24

>>Create 1 index column called "Sales Index" in Sales table in Power Query.

>>Create 1 index column called "GRC Index" in GRC table in Power Query.

>>Create this calculated column in the Sales Table.

Note : this solution only work on Index Column If you change your data so may be this solution is not work.