topic Re: Create Measure From Virtual Table in DAX Commands and Tips

Create Measure From Virtual Table

mmahoney045 — Mon, 17 Jun 2024 13:48:38 GMT

Hello,

I am attempting to create a measure that will return the distinct count of a specific type of customer (New Tank Distributor) that is set up in any given year. After attempting simple measure and calculated table approaches, I believe the only solution that will work for my situation is a virtual table(s) that is then aggregated down to the single scalar value: the sum or count of New Tank Distrubtors over any filtered period of time.

Here are the two variable virtual tables I've create which get me very close. Only thing I am missing is how to produce a single value for New Tank Distributor instead of a row that contain's its label & count. Any advice how to rework the second VAR to produce a scalar value instead of table?

Note: I label each "distributor type" in the first virtual table because once I figure out how to create this measure, I will reproduce it for the other distrubutor types as well.

New Tank Distributor =

VAR _summarytable =

SUMMARIZECOLUMNS(

'table'[Customer],

'table'[InvoiceDate],

"CY Tank Sales", CALCULATE(sum('table'[tank revenue])),

"CY All Sales", [Sales ALL Total],

"Previous 2 Years All Sales", CALCULATE([Sales ALL Total], dateadd('table'[InvoiceDate].[Date],-2,YEAR)) + CALCULATE([Sales ALL Total], dateadd('table'[InvoiceDate].[Date],-1,YEAR)),

"Previous 2 Years Tank Sales", CALCULATE([Sales ALL Total], 'Product Hierarchy'[Product Category] = "Tanks", dateadd('table'[InvoiceDate].[Date],-2,YEAR)) + CALCULATE([Sales ALL Total],'Product Hierarchy'[Product Category] = "Tanks", dateadd('table'[InvoiceDate].[Date],-1,YEAR)),

"Distributor Type",

SWITCH(

TRUE(),

AND(CALCULATE([Sales 2YRS Prior], 'Product Hierarchy'[Product Category] = "Tanks") < 0.01, CALCULATE([Sales ALL Total], 'Product Hierarchy'[Product Category] = "Tanks") > 0), "New Tank Distributor",

AND([Sales 2YRS Prior] < 0.01, [Sales ALL Total] > 0),"New Customer",

OR(CALCULATE([Sales 2YRS Prior], 'Product Hierarchy'[Product Category] = "Tanks") > 0, CALCULATE([Sales ALL Total], 'Product Hierarchy'[Product Category] = "Tanks") > 0), "Existing Tank Distributor",

OR([Sales 2YRS Prior] > 0, [Sales ALL Total] > 0),"Existing Customer","Potential Customer")

)

VAR _aggtable =

FILTER(

GROUPBY(

_summarytable,

[Distributor Type],

"Number Of Customers", COUNTX( CURRENTGROUP(),1)), [Distributor Type]="New Tank Distributor")

RETURN

_aggtable

(_aggtable produces a single count or sum of New Tank Distributors)

Re: Create Measure From Virtual Table

Daniel29195 — Mon, 17 Jun 2024 15:36:45 GMT

mmahoney045

to return a scalar value from the second table , assuming that this returns only one row :

VAR _aggtable =
selectcolumns(,
        FILTER(
            GROUPBY(
                _summarytable,
                [Distributor Type],
                "Number Of Customers", COUNTX( CURRENTGROUP(),1)
            ),
        [Distributor Type]="New Tank Distributor"
        ),
        [Number Of Customers]
        )

if it returns multiple rows, then you need to either filter to one row, or to use one of the iterators base on your business logic :
sumx , maxx, minx, . ..

let me know if thelps.

Re: Create Measure From Virtual Table

mmahoney045 — Mon, 17 Jun 2024 16:04:52 GMT

Thanks for the response!

Your solution does help to return a single row and column, which is the closest I've come so far. However it does not appear it is a pure scalar value because I cannot throw it in a card to display the single value on my dashboard.

Is it possible to run one of the aggregator functions over to entire result of the second virtual table?

Thank you,

Matt

Re: Create Measure From Virtual Table

Daniel29195 — Mon, 17 Jun 2024 16:35:50 GMT

you can use maxx or minx if you are sure that you have 1 value . or you have multiple values all the same. mmahoney045

Re: Create Measure From Virtual Table

mmahoney045 — Mon, 17 Jun 2024 17:03:07 GMT

It appears to be only a single value, judging from the output. How would the MAXX wrap arround the groupby statment? I tried, but my attempt failed.

[Number Of Customers]
"1182"

Re: Create Measure From Virtual Table

Daniel29195 — Mon, 17 Jun 2024 17:10:25 GMT

mmahoney045

VAR _aggtable =
maxx(,
        FILTER(
            GROUPBY(
                _summarytable,
                [Distributor Type],
                "Number Of Customers", COUNTX( CURRENTGROUP(),1)
            ),
        [Distributor Type]="New Tank Distributor"
        ),
        [Number Of Customers]
        )

should be something like this.

Re: Create Measure From Virtual Table

mmahoney045 — Mon, 17 Jun 2024 17:19:24 GMT

Ah, sorry. the error states: The 3 argument to the MAXX function contains a flag that is not supported.

Its bizarre that converting a table value to scalar is this difficult.

Re: Create Measure From Virtual Table

Daniel29195 — Mon, 17 Jun 2024 17:26:59 GMT

mmahoney045

can you please share a screenshot of the code ?

Re: Create Measure From Virtual Table

mmahoney045 — Mon, 17 Jun 2024 17:42:28 GMT

Here is the code:

VAR _aggtable =

MAXX(,

FILTER(

GROUPBY(

_summarytable,

[Distributor Type],

"Number Of Customers", COUNTX( CURRENTGROUP(),1)), [Distributor Type]="New Tank Distributor"),[Number Of Customers])

EVALUATE

_aggtable

Re: Create Measure From Virtual Table

Daniel29195 — Mon, 17 Jun 2024 17:53:45 GMT

mmahoney045

remove the comma near maxx ( filter ( .... ) , [ ] )

mmahoney045

Re: Create Measure From Virtual Table

mmahoney045 — Mon, 17 Jun 2024 18:02:17 GMT

I tried that and unfortunately the error changes to "The expression specified in the query is not a valid table expression".

Re: Create Measure From Virtual Table

Daniel29195 — Mon, 17 Jun 2024 18:05:49 GMT

mmahoney045

because it is returning a scalar value, you cant use it like this when authoring a dax query

to be able to see it in the query editor,, write the evaluate like this :
evaluate { _aggtable }

Re: Create Measure From Virtual Table

mmahoney045 — Mon, 17 Jun 2024 18:28:00 GMT

It worked in DAX editor , but not when I went to set up the measure in the visualization page. Any other changes necessary to make this scalar work in a measure?

DAX editor result:

Measure Code:

Re: Create Measure From Virtual Table

Daniel29195 — Mon, 17 Jun 2024 18:30:52 GMT

well, you can t use summarizecolumns in measure

try using addccolumns ( summarize ( .. ) instead .

and for the return, just return aggregation without the { } .

the { } were only needed for the query not to be used in the measure.

hope this makes sense.

mmahoney045

Re: Create Measure From Virtual Table

mmahoney045 — Sat, 22 Jun 2024 13:57:46 GMT

This worked Daniel. Thank you!