topic Create table based on DAX in DAX Commands and Tips

Create table based on DAX

joshua1990 — Fri, 03 Jun 2022 12:55:37 GMT

Hi community!

I have a data with 1 dimensional table and different transaction tables.

Dim 1 : Country, Department, Team, Product
Calendar table
1. Transaction Table: Sales
2. Transaction Table: Revenue
3. Transaction Table: Costs

The tables are linked by Product and Date.

Now I would like to create a calculated table that contains this format:

Country	Area	Team	KPI	Level	Value
France	AA	A1	Sales	Good	5
France	AA	A1	Sales	Neutral	6
France	AA	A1	Sales	Bad	8
France	AA	A1	Costs	Good	8
France	AA	A1	Costs	Neutral	6
France	AA	A1	Costs	Bad	5

As you can see we have 3 columns with the dimensions Country, Area and Team.

Then we have the specific KPI (Sales, Costs, Revenue) with a specific "Level" and value.

The value shows the frequence/ bumber of weeks we have been able to reach a specific goal:

Sales

Good: Sales above 500
Neutral: Sales 200-500
Bad: Sales below 200

How is this possible with dax?

I don't need this information as a matrix, that would be easy.

I need this as a calculated table.

I am grateful for every help!

Re: Create table based on DAX

tamerj1 — Fri, 03 Jun 2022 13:50:38 GMT

H joshua1990

Can you provide some sample data to work with?

Re: Create table based on DAX

Anonymous — Tue, 07 Jun 2022 08:53:28 GMT

Hi joshua1990 ,

I created some data:

For example:

Transaction Table: Costs

When the three table structures of Transaction Table: Sales Transaction Table: Revenue Transaction Table: Costs are the same, we can use the Append method.

Here are the steps you can follow：

1. Go to Power query and select Append Queries -- Append Queries as New.

Enter the Append interface, select "Three or more tables", add three tables.

Result：

2. Select the created new table Append1, select [Sales],[Revenue],[Costs] – Unpivot Columns.

3. Select the two columns in the new data and change the names to "KPI", "Amount”.

4. Create calculated column.

Level = SWITCH( TRUE(), 'Append1'[Amount]<=200,"Bad", 'Append1'[Amount]>200 &&'Append1'[Amount]<=500,"Neutral", 'Append1'[Amount]>500,"Good")

Value = COUNTX(FILTER(ALL('Append1'),'Append1'[KPI]=EARLIER('Append1'[KPI])&&'Append1'[Level]=EARLIER('Append1'[Level])),[Level])

5. Create calculated table.

Table = SUMMARIZE('Append1', 'Append1'[Country],'Append1'[Area],'Append1'[Team],'Append1'[KPI],'Append1'[Level],'Append1'[Value])

6. Result:

Best Regards,

Liu Yang

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