Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Compare Value of amount from 2 unrelated tables by column

hey, 

 

I will try my best to explain my situation here, so if you have any questions, please ask.

 

we have PBIX that we're working on under IMPORT mode. 

 

we have 2 fact tables:

 

1 - Revenue

2- FACT_INVOICE (2) (same as revenue) 

 

CSDEV_0-1642327376199.png

 

they are connected to the same Dim's but not to the same Dim Date table 

 

so Revenue connected to Dim_Date 

and FACT_INVOICE (2) connected to Dim_Date 2

why?

because we want to compare periods 

 

so we have 2 periods to choose from and we have a table that aggregates the data

 

CSDEV_1-1642327527815.png

 

 

under this, we have a table that does not connect to anything, with values:

 

CSDEV_2-1642327594715.png

 

now, for each "Model" (item) on the aggregated table, we need to determine if the item is whether "New" or "Old" (ignore "both") 

 

New = The item sold in Period 1 but NOT in Period 2 

Old =  The item Sold in Period 2 but NOT in Period 1 

 

this is the Calculation for this:

 

Old & New = if('Table'[Measure] = "New", "Old & New" , if('Table'[Measure] = "Both", "Both",BLANK()))
 
New Vs Old M = if(
[Sum Amount USD Invoice] > -999999999999 && [Sum Amount USD Invoice 2] <= 0 || [Sum Amount USD Invoice 2] > -999999999999 && [Sum Amount USD Invoice] <= 0 , "New",
if([Sum Amount USD Invoice] > -999999999999 && [Sum Amount USD Invoice 2] > -999999999999 , "Both"))
 
the measure works as expected, and the results are fine. 
 
however, the measure is only working while in a table with the "Model" column from Dim_Item Table. 
if it's removed (the "Model" column) from the table, the measure cant decide whether it's old/new.
 
I need to create a measure of "New Impact" and "Old Impact" but I don't know how to calculate that, as the New/Old attribute is not related to any table, it's just logical. 
 
so when I'm trying to do something like: Calculate(Sum(amount), Measure = "Old") it's just calculating the whole revenue of the selected period because "Old" is just a logical attribute and there's no "Model" in the context. 
 
I would like to know if I can calculate the old impact and new impact, in a card, not in a table, without the need for the "Model" column 
 

New Impact = The item sold in Period 1 but NOT in Period 2 

Old Impact =  The item Sold in Period 2 but NOT in Period 1 

 

just a couple of pics:

 

this is New Impact inside the table with model column (right results):

CSDEV_3-1642328245873.png

 

 

when I remove the model column (bad results, calculating the whole period amount and not only  new):

 

CSDEV_4-1642328272698.png

 

 

 
 

 

 

1 REPLY 1
ValtteriN
Super User
Super User

Hi,

I am not 100% I understood your problem specifically, but from what I can tell I have had similar cases where I wanted to create a category measure and use this to make calculations. Typically this is in the context of customer categories e.g. "New customer","Old customer"...

The typical way that I use to solve these kinds of problems is to first create category measure (in your case [Old & New]). 

Now we  can use this category measure combined with SUMX, or e.g. COUNTX to make our calculations. Here is an example:

Category measure:

Category = IF(SELECTEDVALUE(TotalToZero[Subaccount])="c","c","")

C revenue =
SUMX(filter(TotalToZero,[Category]="c"),TotalToZero[Value])
 
End result:
ValtteriN_0-1642348475803.png


By using this logic we can combat this issue:
"so when I'm trying to do something like: Calculate(Sum(amount), Measure = "Old") it's just calculating the whole revenue of the selected period because "Old" is just a logical attribute and there's no "Model" in the context. "

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.