Forum Discussion

johnfa's avatar
johnfa
Frequent Visitor
6 years ago
Solved

Measure to sum another measure problem

Hi there I am new to DAX and having problems calculating a measure to sum another measure and was hoping someone could point me in the right direction.  I have simplified the data below to show th...
Need Help
  • YJ's avatar
    YJ
    6 years ago

    Hi ,

    Under Modeling , New Table put this in:

     

     Tb_Products_w_no_Sales = SUMMARIZECOLUMNS(Table1[Date ],

    (Filter (
    SUMMARIZECOLUMNS(Table1[Date ],Table1[Product],"ZERO_S",IF(SUMX(Table1,Table1[Sales])=0,0,1)),[ZERO_S]=0
    )
    ),"Products_w_no_Sales",DISTINCTCOUNT(Table1[Product])
    )
     
    *overall idea, ZERO_S will give 0 for rows of products with no sales, and after do a distinct count on product
    where Table1 is your data.
     
    This should be what you want. i believe 
    02/01/20192
    is not correct, it should be 3. but let me know.
     
    If you need a 0 on the missing dates, link it to a date table or left join it.
     
    Regards
     
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi johnfa ,

     

    We can create a calcualted column and new a measure based on it.

    per product = 
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        'Table',
        'Table'[Date ] = EARLIER ( 'Table'[Date ] )
            && 'Table'[Product] = EARLIER ( 'Table'[Product] )
    )
)

    Measure = CALCULATE(DISTINCTCOUNT('Table'[Product]),FILTER('Table','Table'[per product] = 0))

     

johnfa's avatar
johnfa
Frequent Visitor
6 years ago

Thanks for taking the time to explain it fully thats really helpful. Being new to DAX it is certainly easier to follow if every step is broken down explicitly into a separate table although obviously a bit long winded.   It is frustrating that you cant refer to columns created in the inner table in the outer table in the same block ie "Products_w_no_Sales" -  I am used to working with SQL where you can easily create aliases for datasets and columns and refer to them later.   Would it be possible to assign the inner table to a variable and then use that later to refer to all the columns we want ?

YJ's avatar
YJ
Resolver II
6 years ago

Hi, Jo 

afaik, its is not yet possible to declare global var in DAX:

https://community.powerbi.com/t5/Developer/Is-it-possible-to-have-global-variable-for-whole-report/td-p/349861

 

However for improve readaiblity, its possible to have  local var:

Tb_Products_w_no_Sales =

Var

InnerTable= SUMMARIZECOLUMNS(Table1[Date ],Table1[Product],"ZERO_S",IF(SUMX(Table1,Table1[Sales])=0,0,1)

Return

SUMMARIZECOLUMNS(Table1[Date ],

(Filter (InnerTable),[ZERO_S]=0)),

"Products_w_no_Sales",

DISTINCTCOUNT(Table1[Product])

)
 
Of course with the indentation in powerbi it should look clearer there.( i am sorry that i didnt indent it well )
I understand how you feel , for something similiar to SQL, (use VAR as above)
WITH MYTABLE 
AS
() 
SELECT *
FROM BASETABLE
 
but for alias , SELECT * from BASETABLE as ALIAS_MYTABLE, i think there is nothing  similiar...
 
My personal preference when working with Powerbi and SQL is to have a view or table function on the SQL server and have powerbi query from that to do measures, YTY , simple agregation and reporting. But i am sure this is subjective =)
 
regards