cancel
Showing results for
Did you mean:
Highlighted
svalen Member

Context on Measure (with SUMMARIZE) - Connecting Different Tables

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?

Cheers!

3 REPLIES 3 Community Support Team

Re: Context on Measure (with Summarize)

Hi

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

svalen Member

Re: Context on Measure (with Summarize)

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")
svalen Member

Re: Context on Measure (with Summarize)

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

Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 46 members 1,032 guests
Recent signins:
• Arunican88 • Bakhtawar • jhani • marcorusso • jinokannen • akailwoo • George_3029 • PowerUserHM • nsharma • Ngene • troystaylor • mexicobigdata • Bielite 