Context on Measure (with SUMMARIZE) - Connecting Different Tables

[Anonymous]

Hi guys.

I'm doing some calculations with 2 degrees of granularity: ID1 (like client), ID2 (like product) and then the facts (sales, etc.). I want to calculate something like below:

MEASURE = 
CALCULATE (
    SUM ( 'Table1'[Column1] );
    FILTER (
        SUMMARIZE (
            ALL (
                'Table1'[ID1];
                'Table1'[ID2];
                'Table1'[Quarter]
            );
            'Table1'[ID1];
            'Table1'[ID2];
            'Table1'[Quarter];
            "tempmeasure"; [MEASURE1];
            "tempmeasure2"; CALCULATE (
                [MEASURE1];
                SUMMARIZE (
                    ALL ( 'Table1'[ID1]; 'Table1'[Quarter] );
                    'Table1'[ID1];
                    'Table1'[Quarter]
                )
            )
        );
        [tempmeasure] > 0
            && [tempmeasure2] > 0
    )
)

"tempmeasure" means that every "MEASURE1" of ID2 should be greater than zero (it works) and "tempmeasure2" (where it doesn't work) should be the aggregate of that calculation (but only for ID1, bigger group) should be greater than zero.

Do you see any flaws?

Thanks in advance.

Cheers!

[v-yuta-msft]

Hi svalen,

Syntax of your measure seems right, but could you clarify more details about your logic and give some sample data or a mockup so that I can do further analysis? 

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft

I think I got what is wrong but I can't solve it now...

The Measures I refer:

SO UNITS PYG % = 
IF(
	ISFILTERED('Calendar'[Date]);
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");
	VAR __PREV_YEAR = CALCULATE([SO UNITS]; DATEADD('Calendar'[Date].[Date]; -1; YEAR))
	RETURN
		DIVIDE([SO UNITS] - __PREV_YEAR; IF(__PREV_YEAR = 0;1;__PREV_YEAR)
))

---source measure---

SO UNITS = SUM('Table'[UNITS])

Can only be well calculated if the two IDs are the Dimension Tables IDs and not the Fact Tables IDs...

What I need inside the ALL is:

   - a column from Dimension Table 1 (not from Fact table as it was)

   - a column from Dimension Table 2 (not from Fact table as it was)

   - a column (quarter) from fact table to group by

Problem: how can I add tables from different columns inside the ALL function? I can't.

Is there a way around this?

Thanks!

[Anonymous]

Thank you!

Sure.

First of all, I created the table I use for the CALCULATE and it seems absolutely wrong, only one value on the added columns:

Now for the logic:

   - There are two fact tables

   - The Measure I am trying to create is on table 2

   - The Measure that filters inside the CALCULATE is on table 1

   - This Measure is like:

SO UNITS PYG % = 
IF(
	ISFILTERED('Calendar'[Date]);
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");
	VAR __PREV_YEAR = CALCULATE([SO UNITS]; DATEADD('Calendar'[Date].[Date]; -1; YEAR))
	RETURN
		DIVIDE([SO UNITS] - __PREV_YEAR; IF(__PREV_YEAR = 0;1;__PREV_YEAR)
))

---source measure---

SO UNITS = SUM('Table'[UNITS])

   - The relations are well built, of that I am sure because I did the calculation other way (less efficient) and it is right:

The logic inside SUMMARIZE is that:

   - There are 2 IDs (like client and product)

   - I want the Measure to be greater than zero for every product

   - Also, I want the measure to be greater than zero for the Client aggregation

I also think that the code is right, but with the table I created it seems really off.

I hope this is enough to help you help me.

Thanks!

----------EDIT----------

I am basing the calculations on Quarters and I created an extra column on the fact table I'm trying to create the measure in like:

Quarter_FACT = FORMAT(STARTOFQUARTER('FACT'[Date]);"mm-yyyy")