topic Re: How to generate a new table base on some conditions in DAX Commands and Tips

How to generate a new table base on some conditions

Tracy000 — Thu, 14 Mar 2024 07:21:05 GMT

Hi team,

Good day, I got a problem which need your kind advice, thanks in advance.

I have a table1 in Power BI, the data is as following:

Now I want to create another table (table2) base on table1, but only select few data base on conditions 1 & 2:
1. Only select these 4 columns data: GEOGRAPHY.03 MARKET, FISCAL TIME.04 FISCAL MONTH, PRODUCT LINE, AMT
2. Only the sum of AMT for GEOGRAPHY.02 SUPER REGION is not 0 should show in this table2
so this means only row 9-13 meet this condition, because row 1-8 sum AMT for SUPER REGION Asia Pacific is 0, although row 13 contains 0, but sum of AMT for Americas is not 0.

So finally I would like to generate the table 2 like following:

Re: How to generate a new table base on some conditions

Dangar332 — Thu, 14 Mar 2024 07:35:49 GMT

bhi, Tracy000

try below code for new table it might work

newtable= calculatetable( summarize( table1[geography.03market], table1[fiscaltime.04fiscalmonth], table1[productline], table1[amt] ), filter( values(table1[GEOGRAPHY.02 SUPER REGION]), sum(table1[amt])>0 ) )

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

Re: How to generate a new table base on some conditions

Tracy000 — Thu, 14 Mar 2024 08:11:52 GMT

Dangar332
Thanks a lot for your quick reply!

I'm really sorry that I missed one very important point in condition2 in the original post:
2. Only the sum of AMT for GEOGRAPHY.02 SUPER REGION and PRODUCT LINE is not 0 should show in this table2.

(The AMT should be based on both GEOGRAPHY.02 SUPER REGION and PRODUCT LINE level)

Re: How to generate a new table base on some conditions

Tracy000 — Thu, 14 Mar 2024 09:29:11 GMT

Dangar332
Seems I got the result I want.

Added a variable to keep the filters and then applied to table2 as you suggested above.
var table1 = SUMMARIZECOLUMNS([GEOGRAPHY.02 SUPER REGION], [PRODUCT LINE], "SUMAMT", sum([amt]))

Thank you!