DAX Summarize based on Another Summarized Table RRS feed

  • Question

  • Hi

    I want to Summarize  two tables expression i.e one as an input to another  . I am using variables in my calculated Measure. 

    Sales is the original table in my tabular model and [Net_Sales] is a measure based out of it. For simplicity I have not given the expression for Net Sales.

    4PC := 

    Var temptable1 = Summarize(Sales,Sales[distributor],Sales[dealer],Sales[Model],"NS",Sales[Net_Sales]) 

    Var temptable2 = Summarize(temptable1,Sales[distributor],Sales[dealer],"ProductCount",CALCULATE(DISTINCTCOUNT(Sales[Model]),FILTER(temptable1,[NS]>0))) Return CALCULATE(DISTINCTCOUNT(Sales[DistDealerKey]),FILTER(temptable2 ,[ProductCount]=4))

    My question is will the temptable2 summarize for productcount based on temptable1? . As you can see I have already summarized temptable1 for netsales by distributor,dealer and Model based on by Sales table 

    Thursday, May 28, 2020 5:50 PM

All replies

  • Hi Prabhu64,

    As per my understanding of this case , your measure expression, looks valid for me. It should be ok.

    I tested with Adventure Works data within my environment, it works.

    But, Seems to me there is an error in your temptable1?

    Var temptable1 = Summarize(Sales,Sales[distributor],Sales[dealer],Sales[Model],"NS",SUM(Sales[Net_Sales])) 

    There should be some aggregation function in the sum I placed.

    If you still don't feel confident in this.I suggest you create two calculated tables for this two temp table. Then created the measure with help of the 2nd temp table. The result should a be more clear proof.



    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Friday, May 29, 2020 7:35 AM
  • It certainly should because lineage is preserved in your groupings. Yet, why don't you try counting rows of the corresponding temp table instead of doing a distinct count off the base table filtered by the temp? Also, it seems that you could use just the DistDealerKey instead of two columns, distributor and dealer.

    Expect me to help you solve your problems, not to solve your problems for you.

    Friday, May 29, 2020 7:55 AM