Is it possible to summarize the columns of a virtual table in DAX?

DAX DAX Calculations
1

Hi folks,

Below is a dax code which is creating virtual table with 6 columns.

Define
VAR A =
ADDCOLUMNS (
CROSSJOIN( SUMMARIZE( Destinations, Destinations[Dest]),SUMMARIZE(‘Material Master’,‘Material Master’[Material])),
“Forecast_Act_OrdersQty”, [Order Qty (Combined)],
“Forecast_OrdersQty”, [Supply Forecast(M-3 logic Based on Latest Forecast File)],
“Variance”, [Forecast Variance]

)

VAR B =
ADDCOLUMNS (
A,
“FA_Denominator”,
IF (
[Forecast Variance] > 0,
[Forecast_OrdersQty],
[Forecast_Act_OrdersQty]
)
)
Evaluate
B

This dax query results in a table with 6 columns in dax studio . I am trying to create another table from the variable B table that will have 3 columns. First Column will be the unique or distinct values of [Dest] Column and the other two column will be the summarization of [Variance] and [FA_Denominator] column as per the [Dest] column.

I have done it using “Table” keyword which was recently introduced but “table” keyword is not working in PBI. It is only working in Dax studio.

Has anyone done anything like this before using variables only??

Regards,
Hemant

2

@Hemantsingh
as of now TABLE/COLUMN are only available for querying the model and not for enriching the model.

You can use GROUPBY:

image
image1022×879 46.5 KB

image
image1040×720 33 KB

1 Like
3

@Hemantsingh,

I do this all the time in my forum solutions. Rather than writing one complex virtual table measure, I break it down into a series of variables so that it’s easier to follow the thought flow behind the solution.

So in your case you can call VAR B as the table argument in a subsequent SUMMARIZE command:

VAR C =
SUMMARIZE(
     VAR B,
     Destinations[Dest],
     [Variance],
     [FA Denominator]
)

Two other things will help here:

  1. Format your DAX! (as Marco Russo says, "if it’s not formatted, it’s not DAX)
  2. Use the @ convention to distinguish virtual table columns from measures (see article below):

I hope this is helpful.

  • Brian
4

Hi @BrianJ, @AntrikshSharma,

First of all missed you guys and this forum a lot. Was away on a tour hence stayed away from this fantastic forum for quite sometime.

@BrianJ,
The way you are summarizing the variable will summarize 3 columns simultaneously. In other words you will have multiple rows and the values in the [Dest] column will be repeated but each row will be unique. The result i am expecting cannot be achieved with this way of summarization. Also,my apologies that i didn’t format the code before posting. Point well noted and will keep in mind for future posts.

@AntrikshSharma
The way you have summarized the virtual table and the corresponding result is something I believe can be used to complete the scenario i am trying to solve.

Regards,
Hemant

5

@Hemantsingh Yup, here is an example of such a scenario:

image
image1651×788 84.5 KB

EVALUATE
VAR A = 
	ADDCOLUMNS ( 
		CROSSJOIN ( VALUES ( Products[Color] ), VALUES ( Customer[Gender] ) ),
		"@Sales", [Total Sales],
		"@Perc of Total", DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], REMOVEFILTERS() ) ) * 100
	)
VAR ABCClassification = 
	ADDCOLUMNS ( 
		A,
		"@ABC", SWITCH ( TRUE(), [@Perc of Total] <= 10, "A", [@Perc of Total] <= 15, "B", "C" )
	)
VAR ABCGrouping = 
	GROUPBY ( 
		ABCClassification,
		[@ABC],
		"ABC Sales", SUMX ( CURRENTGROUP(), [@Sales] )
	)
RETURN
	ABCGrouping
5 Likes
6

@Hemantsingh,

Great to have you back. Sorry I missed the mark on this, but great that @AntrikshSharma provided an excellent solution.

  • Brian
1 Like