topic Re: How to select a subset of a table by a Date Slicer and then group it twice by multiple columns? in DAX Commands and Tips

How to select a subset of a table by a Date Slicer and then group it twice by multiple columns?

MyPI — Mon, 20 Feb 2023 16:39:20 GMT

Input Table1

Use a Date slicer to pick up the date range from 11/29/2022 to 11/30/2022 get Table 2:

Calculate the Total_Availability_By_Group by groupping the Product_Name, Product_Type and Availability_Type to get Table 3:

Finally, calculate the IB-OB by groupping the Product_Name and the Product_Type to get Table 4:

Any idea how to get the IB-OB or the Table 4 with Dax functions? Thank you!

Re: How to select a subset of a table by a Date Slicer and then group it twice by multiple columns?

wdx223_Daniel — Tue, 21 Feb 2023 02:40:54 GMT

put Product_Name and Product_Type on the rows and try this measure

IB-OB=CALCULATE(SUM(Table[Availablity]),Table[Availability_Type]="IB")-CALCULATE(SUM(Table[Availablity]),Table[Availability_Type]="OB")

Re: How to select a subset of a table by a Date Slicer and then group it twice by multiple columns?

MyPI — Tue, 21 Feb 2023 16:04:56 GMT

Hi wdx223_Daniel,

Thank you for your suggestion.

However, all I have is the input Table 1, whereas the Table 2, Table 3 and Table 4 are just virtual tables which are assume to be obtained after the processing specified at each step. What I would like to know is:
1. how to get Table 2 from Table 1 with dax function?

2. how to get Table 3 from Table 2 with dax function?

3. how to get Table 4 from Table 3 with dax function?

In other words, what is the right dax function to get the IB-OB from Table?