Combining data in Matrix/Table with Measures from different tables

[lealpermalino]

I have two tables (Daily & Weekly) and created similar measures on both. However, when combined it in a matrix/table, it gives me wrong values.

Both tables have multiple values per ID and is linked through ID_Key table

 

Left table is the weekly data and on the right is the combined daily and weekly tables

Left/Weekly table values are correct.

See column "Q1 Cap Wk", values are different. Tho, seems conditional formatting follows the correct data (Formatting condition: Q1 Cap Wk >= BASE (-) Weekly should be "BLUE", combined table is formatted it to "RED")

Below code for Q1 Cap Wk:

Q1_Cap_Weekly =

CALCULATE

(PERCENTILE.INC('(1) Weekly_Data'[Base (-)], 0.25),

'(1) Weekly_Data'[Base (-)]<=-1,

'(1) Weekly_Data'[*TREND_Weekly]="Downtrend",

ALLEXCEPT('(1) Weekly_Data', '(1) Weekly_Data'[*Cap])

)

*** note: [*Cap] Row/filter is included but hidden in both tables ****

[v-alq-msft]

Hi, @lealpermalino 

Based on your description, I have something to comfirm. I wonder 'Model' view which shows the relationships and filter directions between tables. Do mask sensitive data before uploading. Thanks.

Best Regards

Allan

[lealpermalino]

Thank you for your response.

I was able to resolve matrix value error by adding removefilters in calculate. But it become very slow. I only put few measures, I need to add more. 

Is there a way to improve PBI performance measures with heavy data caluculations? Or are there any option to this?

Q1_Cap_Weekly =

CALCULATE

(PERCENTILE.INC('(1) Weekly_Data'[Base (-)], 0.25),

REMOVEFILTERS('(1) Daily_Data'[Base (-)]),

'(1) Weekly_Data'[Base (-)]<=-1,

'(1) Weekly_Data'[*TREND_Weekly]="Downtrend",

ALLEXCEPT('(1) Weekly_Data', '(1) Weekly_Data'[*Cap])

)

With regards to model table data flow. All are set to "BOTH directions". daily and weekly table consist of multiple values and is both linked to an ID table

[v-alq-msft]

Hi, @lealpermalino 

You may try to use variables to cache values, which may avoid unnecessary calculations. If the formula repeats the expression that calculates the same thing, this formula is inefficient, as it requires Power BI to evaluate the same expression twice or even more times. The measure definition can be made more efficient by using a variable.

For further information, you may refer to the following links.

https://docs.microsoft.com/en-us/power-bi/guidance/dax-variables

https://community.powerbi.com/t5/Desktop/Improve-Measure-performance/m-p/709629

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lealpermalino]

I tried to use variable but does not make so much difference.

Variable maybe helpful if there are repetetive calculations.

The battleneck I see is removing the filters from the other table(Daily), tried to remove filters one by one by columns but still not enough to improve the performace.

Is it possible to calculate percentile in COLUMN instead of MEASURE?
I was trying to do this but didnt suceed yet.