Create Measure From Virtual Table

[mmahoney045]

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)

[Daniel29195]

@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. 

[mmahoney045]

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

[Daniel29195]

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

[mmahoney045]

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"

[Daniel29195]

@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.

[mmahoney045]

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. 

[Daniel29195]

@mmahoney045 

can you please share a screenshot of the code ? 

[mmahoney045]

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

[Daniel29195]

@mmahoney045 

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

@mmahoney045 

[mmahoney045]

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

[Daniel29195]

@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 } 

[mmahoney045]

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:

[Daniel29195 · Accepted solution]

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 

[mmahoney045]

This worked Daniel. Thank you!