Compare two columns on two different tables and create a new record if the criteria is met

Anonymous — Thu, 29 Oct 2020 16:21:16 GMT

Hello,

I am new to DAX, getting better everyday but I feel like this issue is beyond my reach on my own - hopefully you'll be able to help me.

Issue : I'd like to compare two values from two different tables (on a 1-1 relationship) and if a criteria is met, create a record on a third table.

I don't have permissions to upload images, but I'll explain it as best as I can.

My data model is :

Table KPI_Today (KPIs of the current day) ;
Table KPI_L7D (Average of each KPIs on the last seven days) ;

The relationship is 1-1 and done on a Primary Key ("Hour" column).
Both tables have the exact same column names, and I have one line for one hour of the day (so 24 rows fo each of them).

Goal : I want to create a third table which will be called T_Alerts that will compare one KPI (for now, then I'd like to compare more KPIs) from one table to the other same KPI on the other one (on the same hour, which is the PK).

Example : I want to check if my "Orders" on table KPI_Today is not 20% higher/lower than my "Orders" on KPI_L7D on each hour, and if yes, create a record on the table T_Alerts with the following columns : "Alerte", "Orders", "Hour", "Delta" (the %change).

Does it make sense ?
I feel like it's not an easy one but in the same time, I'm sure it has already been done.

Thanks a lot for any tips, leads, solutions!
Alex

Re: Compare two columns on two different tables and create a new record if the criteria is met

amitchandak — Fri, 30 Oct 2020 01:56:04 GMT

Anonymous , If they both are joined, Summarize should allow you to select data from both tables, even if one table is used in table name.

Of the top of that, you can use the filter

Filter(Summarize(Table1, Table[Col1], Table2[col2]), [Col1]=[Col2])

if they have excat same name then Summarize will not allow rename, You can use selectcolumns for one table.

Also refer

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

and https://radacad.com/append-vs-merge-in-power-bi-and-power-query

topic Re: Compare two columns on two different tables and create a new record if the criteria is met in DAX Commands and Tips

Compare two columns on two different tables and create a new record if the criteria is met

Re: Compare two columns on two different tables and create a new record if the criteria is met