topic How to create a new table base on another table column with condition in DAX Commands and Tips

How to create a new table base on another table column with condition

kentchiu — Fri, 06 Sep 2024 22:30:19 GMT

Hi,

I am a new Power BI user and am having difficulty creating a table based on another table row with conditions.

What I would like to achieve is creating a new table with a distinct row of unique ISSUE_KEY with the latest created date) .

Table A:

CREATED = Date type

ISSUE_KEY = Character String type

New table:

Contains a unique row of the ISSUE_KEY with the latest CREATED date

- Notice there are duplicate rows of ISSUE_KEY with different CREATED dates (could be 2 or more)
- In the sample table above, there are 2 ISSUE_KEY with different CREATED dates. The new table should have the latest date.

Thank you so much for your help.

--Kent

Re: How to create a new table base on another table column with condition

xifeng_L — Sat, 07 Sep 2024 03:32:23 GMT

Hi kentchiu

You can try below DAX Table expression.

New Table = FILTER ( Table, VAR CurCreated = Table[CREATED] VAR LatestCreated = CALCULATE (MAX(Table[CREATED]),ALLEXCEPT (Table,Table[ISSUE_KEY])) RETURN CurCreated = LatestCreated )

But in your this case, the best method is use the PowerQuery to create the new table, which only need two step.

Step1. Sort by ISSUE_KEY and CREATED (The CREATED need to sort in descending)

Step2. Perform deduplication on on the table based on ISSUE_KEY

Because the deduplication operation in PowerQuery will only retain the first occurrence of the row.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Re: How to create a new table base on another table column with condition

sanalytics — Sat, 07 Sep 2024 12:11:20 GMT

Hello kentchiu

You can achieve this by using both DAX and PQ.
Below is the DAX query

VAR _1 = ADDCOLUMNS( SUMMARIZE( Source, Source[ISSUE_KEY], Source[Issue Status Name] ), "@Created",CALCULATE( MAX(Source[CREATED]) ) ) RETURN _1

PQ solution

let Source = Source, #"Grouped Rows" = Table.Group(Source, {"ISSUE_KEY", "Issue Status Name"}, {{"Created", each List.Max([CREATED]), type nullable datetime}}) in #"Grouped Rows"

Below attached file

https://we.tl/t-tJvge3FSTD

Let me know if it helps

Regards,

sanalytics

If it is your solution then please like and accept it as solution

Re: How to create a new table base on another table column with condition

Anonymous — Wed, 11 Sep 2024 08:10:56 GMT

Hi kentchiu

First of all, thanks to xifeng_L and sanalytics for their positive response!

I agree with the approach offered by both of them, and if their replies helped you solve your problem, please consider accepting their replies as a solution that will benefit others who are experiencing similar problems.

If you need any other help, please feel free to contact me.

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

Re: How to create a new table base on another table column with condition

kentchiu — Wed, 11 Sep 2024 18:04:22 GMT

Thank you both sanalytics and xifeng_L for the solution to my inquiry. They seem to work with both proposed approach solutions. I have tried them and it works as I expected.