topic Re: How to retrieve the data of a higher occurrence in DAX Commands and Tips

How to retrieve the data of a higher occurrence

MarcoCast — Mon, 09 Jan 2023 10:50:50 GMT

Hi,

I have Column A and Column B with two references.

Columns A and B talk about the same thing.

Column A is the current reference.
Column B is the previous reference of column A.

Column C gives the date of reference A.

I am looking to have a column D which gives the date of the reference B based on column C.

An example of what I have in mind:

Thanks !

Can you help me please ?

Re: How to retrieve the data of a higher occurrence

v-yueyunzh-msft — Tue, 10 Jan 2023 02:40:54 GMT

Hi , MarcoCast

Here are the steps you can refer to :
(1)We can click "Custom Column" and enter this:

(x)=> if List.IsEmpty( Table.SelectRows(#"Changed Type",(y)=> y[A]=x[B])[C] ) then null else Table.SelectRows(#"Changed Type",(y)=> y[A]=x[B])[C]{0}

(2)Then we can meet your need:

Second, you need to delete the "each" if it exist !

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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

Re: How to retrieve the data of a higher occurrence

MarcoCast — Tue, 10 Jan 2023 08:44:05 GMT

Hi v-yueyunzh-msft ,

Thanks for you time and for help, It work perfectly !

I don't clearly understand the command but i will delve deeper into the subject.

If anyone passes by looking for similar help, maybe we can add that in the command, #"Changed Type" refers to the previous query and must be changed depending on our processing.

(x)=> if List.IsEmpty( Table.SelectRows(#"Changed Type",(y)=> y[A]=x[B])[C] ) then null else
Table.SelectRows(#"Changed Type",(y)=> y[A]=x[B])[C]{0}

Best Regards,

Marco

Re: How to retrieve the data of a higher occurrence

v-yueyunzh-msft — Tue, 10 Jan 2023 09:17:28 GMT

Hi , MarcoCast

Yes , the #"Changed Type" refers to the previous query . You can use other step who has the [A],[B],[C] columns also ,and you can also create a fixed name to solve this problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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