Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mmahoney045
Regular Visitor

Create Measure From Virtual Table

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)
1 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 

View solution in original post

14 REPLIES 14
Daniel29195
Super User
Super User

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

 


               

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?

 

mmahoney045_0-1718640268731.png

 

Thank you,

 

Matt

 

you can use maxx or minx  if you are sure that you have 1 value . or you have multiple values all the same. @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"

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

 

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. 

@mmahoney045 

can you please share a screenshot of the code ? 

 

Here is the code:

 

mmahoney045_0-1718646098617.png

 

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

EVALUATE
_aggtable

@mmahoney045 

 

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

Daniel29195_0-1718646809901.png

 

@mmahoney045 

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

 

 

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

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:

mmahoney045_4-1718648821594.png

 

 

Measure Code:

mmahoney045_3-1718648809406.png

 

mmahoney045_5-1718648858574.png

 

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 

This worked Daniel. Thank you!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.