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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ChrisGec
Frequent Visitor

Calculate one Rowbased DAX Measure from two virtual Tables

Hallo Guys,

 

currently I'm really struggeling with a request.

I have one table as source which is some kind of Product Pricing timeline but there is no complete set of Data for each day.

 

image.png

 

Now the request is to choose two dates in Power BI and then get the AVERAGEX(PITB price - PITA price). (PIT = Point In Time) 

Products that are not valid on one of the PITs shouldn't be included.

 

What I did so far:

I seperated the time in two tabels and made one active and one inactive reference.

image.png

 

Then I made a Measure like this:

 

test = 
VAR PITA = 
IF(HASONEVALUE(TimeA[date]);
 FIRSTNONBLANK(TimeA[date];TimeA[date]);
BLANK())
VAR PITB =
IF(HASONEVALUE(TimeB[date]);
 FIRSTNONBLANK(TimeB[date];TimeB[date]);
BLANK())
VAR PITAData = 
CALCULATETABLE('Basic';Basic[date] = PITA)
VAR PITBData = 
CALCULATETABLE('Basic';Basic[date] = PITB)
VAR Combi =
NATURALINNERJOIN(PITAData;PITBData)
RETURN
1

 

 

Now I have several issues to continue. 

1. With NATURALINNERJOIN its not possible to to a join on a certain column. Basically I want to join on PITAData[product] = PITBData[product].

2. I can't reference "Combi" in the RETURN clause. (At least I think I can't do this.)

 

I'm really sorry if there is any related post but i can't find something similar. 

I'm really used to SQL and SSRS where this would be really easy with an INNER self JOIN and 2 Parameters.

 

Every help is appreciated since I'm pretty desprate now.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi there.

 

I had to create a quick model and changed the table names so please "undo" them to fit your tables' names.  Here's the code and it does work now.

 

My Measure = 
VAR __onePitASelected= HASONEFILTER( 'Calendar A'[date] )
VAR __onePitBSelected = HASONEFILTER( 'Calendar B'[Date] )
var __shouldCalculate = __onePitASelected && __onePitBSelected
VAR __pitAProducts = VALUES( Products[Product] )
VAR __pitBProducts =
	CALCULATETABLE(
		VALUES( Products[Product] ),
		USERELATIONSHIP( Products[Date], 'Calendar B'[Date] ),
-- We have to take off any filters that exist
-- on 'Calendar A' because these are two different
-- calendars and activating the second relationship
-- does NOT deactivate the first one because the
-- relationships refer to TWO DIFFERENT TABLES, not
-- the same one. ALL( 'Calendar A' ) ) var __productsInBoth = INTERSECT( __pitAProducts, __pitBProducts ) var __avgAcrossProducts = AVERAGEX( __productsInBoth, var __priceA = CALCULATE( MAX( Products[Price] ) ) var __priceB = CALCULATE( MAX( Products[Price] ), USERELATIONSHIP( Products[Date], 'Calendar B'[Date] ), ALL( 'Calendar A' ) ) return __priceB - __priceA ) return if( __shouldCalculate, __avgAcrossProducts )

 

The lesson to take from this is that when you want your question answered quickly and correctly it's best to supply a file with some example data and formulas so that others have something to work with and can see a tangible and concrete model.

 

By the way, is it necessary to have one of the relationships disabled? You could leave it as active and the model would still work OK (but you'd need to make some small changes to the code above, of course).

 

Best

Darek

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

[Your Measure] :=
VAR __onePitASeleced= HASONEVALUE( TimeA[date]) )
VAR __onePitBSelected = HASONEVALUE( TimeB[date]) )
var __shouldCalculate = __onePitASelected && __onePitBSelected
VAR __pitAProducts = VALUES( Basic[product] )
VAR __pitBProducts =
	CALCULATETable(
		VALUES( Basic[product] ),
		USERELATIONSHIP( Basic[date], TimeB[date] )
	)
var __productsInBoth =
	INTERSECT(
		__pitAProducts,
		__pitBProducts
	)
var __avgAcrossProducts =
	AVERAGEX(
		__productsInBothPits,
		var __priceA = CALCULATE( MAX( Basic[price] ) )
		var __priceB =
			CALCULATE(
				MAX( Basic[price] ),
				USERELATIONSHIP( Basic[date], TimeB[date] )
			)
		return
			__priceB - __priceA
	)
return
	if( __shouldCalculate, __avgAcrossProducts )

Thanks for your responce. I tried your code but i don't get any results on the Front End, even if i select two perfect matching days.

I modified your skript a litte because i got some Syntax errors.

test = 
VAR __onePitASeleced= HASONEVALUE( TimeA[date])
VAR __onePitBSelected = HASONEVALUE( TimeB[date])
var __shouldCalculate = __onePitASeleced && __onePitBSelected
VAR __pitAProducts = VALUES( Basic[productname] )
VAR __pitBProducts =
	CALCULATETable(
		VALUES( Basic[productname] );
		USERELATIONSHIP( Basic[date]; TimeB[date] )
	)
var __productsInBoth =
	INTERSECT(
		__pitAProducts;
		__pitBProducts
	)
var __avgAcrossProducts =
	AVERAGEX(
		__productsInBoth;
		var __priceA = CALCULATE( MAX( Basic[price] ) )
		var __priceB =
			CALCULATE(
				MAX( Basic[price] );
				USERELATIONSHIP( Basic[date]; TimeB[date] )
			)
		return
			__priceB - __priceA
	)
return
	if( __shouldCalculate; __avgAcrossProducts )

Is this probably because __pitBProducts don't get any valid data?

Anonymous
Not applicable

Hi there.

 

I had to create a quick model and changed the table names so please "undo" them to fit your tables' names.  Here's the code and it does work now.

 

My Measure = 
VAR __onePitASelected= HASONEFILTER( 'Calendar A'[date] )
VAR __onePitBSelected = HASONEFILTER( 'Calendar B'[Date] )
var __shouldCalculate = __onePitASelected && __onePitBSelected
VAR __pitAProducts = VALUES( Products[Product] )
VAR __pitBProducts =
	CALCULATETABLE(
		VALUES( Products[Product] ),
		USERELATIONSHIP( Products[Date], 'Calendar B'[Date] ),
-- We have to take off any filters that exist
-- on 'Calendar A' because these are two different
-- calendars and activating the second relationship
-- does NOT deactivate the first one because the
-- relationships refer to TWO DIFFERENT TABLES, not
-- the same one. ALL( 'Calendar A' ) ) var __productsInBoth = INTERSECT( __pitAProducts, __pitBProducts ) var __avgAcrossProducts = AVERAGEX( __productsInBoth, var __priceA = CALCULATE( MAX( Products[Price] ) ) var __priceB = CALCULATE( MAX( Products[Price] ), USERELATIONSHIP( Products[Date], 'Calendar B'[Date] ), ALL( 'Calendar A' ) ) return __priceB - __priceA ) return if( __shouldCalculate, __avgAcrossProducts )

 

The lesson to take from this is that when you want your question answered quickly and correctly it's best to supply a file with some example data and formulas so that others have something to work with and can see a tangible and concrete model.

 

By the way, is it necessary to have one of the relationships disabled? You could leave it as active and the model would still work OK (but you'd need to make some small changes to the code above, of course).

 

Best

Darek

Anonymous
Not applicable

Please send me a link to your .pbix file with the model and data. You can share it via GoogleDrive, OneDrive or Dropbox... or whatever. I authored the measure without any model, just wrote it and have not actually executed it.

 

Once I have the file, I'll be able to give you a fully working solution.

 

Thanks.

 

Best

Darek

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors