Set Analysis instead of Pick

[Yevhenii_Senko]

Hi,

Please see data example below. 

ProductGroup,	StockColor,	StockSize,	StockQuantity,
ProductGroup1,	Green,	40,	10,
ProductGroup1,	Green,	41,	15,
ProductGroup1,	Red,	40,	5,
ProductGroup1,	Red,	41,	5,
ProductGroup2,	Yellow,	Small,	5,
ProductGroup2,	White,	Large,	15,

ProductGroup,	SalesProductName,	SalesColor,	SalesSize,	SalesQuantity,
ProductGroup1,	Shoes,	Green,	40,	3,
ProductGroup1,	Shoes,	Green,	41,	5,
ProductGroup1,	Shoes,	Red,	40,	2,
ProductGroup1,	Shoes,	Red,	41,	2,
ProductGroup2,	Toy,	,	,	11,
ProductGroup2,	Toy,	,	,	8,

In the current data model these two Fact tables are joined by ProductGroup.

However in a chart it should be presented by all dimensions (Group, ProductName, Color, Size) with correct values per row for StockQuantity and SalesQuantity measures.

Some products doesn't have Color and Size therefore key cannot be combined using these columns.

Existing data model is complex. I am thinking to move Products where available Size and Color in a different Table.

 I am curious whether is possible to create an expression with only Set Analysis (e.g. {<StockColor=P(SalesColor), StockSize=P(SalesSize)>} or something like {<StockColor={"=Only(SalesColor)"}, StockSize={"=Only(SalesSize)"}>} )

It doesn't work properly.

In addition I have created the expression with Pick function where result as expected.

Pick(Match(StockColor,'Green','Red')
// Green
,Pick(Match(StockSize,'40','41')
,Sum({<StockColor={'Green'}, SalesSize={'40'}>}StockQuantity) // 40
,Sum({<StockColor={'Green'}, SalesSize={'41'}>}StockQuantity) // 41
)
// Red
,Pick(Match(StockSize,'40','41')
,Sum({<StockColor={'Red'}, SalesSize={'40'}>}StockQuantity) // 40
,Sum({<StockColor={'Red'}, SalesSize={'41'}>}StockQuantity) // 41
)
)

My question is how to replace Pick function with Set Analysis hence leave only one formula?

Any help will be much appreciated.

[Yevhenii_Senko]

Hi @sunny_talwar ,

Could you please take a look at my post?

[sunny_talwar · Accepted solution]

I don't think this can be done using set analysis... one option which might work is to use an if statement.

[Yevhenii_Senko]

Thanks Sunny, I thought the same regarding to Set Analysis.

From different discussions I found pick should work faster than if. Could you please confirm this assumption?

Thanks 

[sunny_talwar]

I think that assumption is more often true, but I would suggest you to test it out for yourself.... Try this

Sum(If(StockColor = SaleColor and SaleSize = StockSize, StockQuantity))

[Yevhenii_Senko]

Thanks much! I will try it!

[Yevhenii_Senko]

I have tested both approaches where identified longer calc time with using pick function.

This testing is acceptable for the existed data model and perhaps might show another results in a different app.

BTW is there only one option to check object resources in the document properties? I mean without using the Document Analyzer. Is it possible to create a chart where to present resources consuming per sheet/objects?

[sunny_talwar]

Document Analyzer and Document properties are the only two places I am aware of where you can check this.