Create Measure from Fact Table

[Anonymous]

I have a Fact table as below and the requirement there is SalesTarget for few ProductdID, OpportunityRecordTypeID and StageID

FactTable:

SalesTarget for few Product, OpportunityRecordType and Stage:

I am looking for a measure that can be aggregated like SUM of SalesTarget can be achieved in visuals similar to SQL as 

Or any other idea that can resolve the problem.

[AlexisOlson]

You can rewrite the SQL with SWITCH ( TRUE, ... ).

SwitchTrue =
SWITCH (
    TRUE,
    Table1[ProductID]
        IN { 4, 6, 11, 34, 35, 38, 43, 44, 45 }
        && Table1[OpportunityRecordTypeID] = 11
        && Table1[StageID] IN { 7, 8, 10, 12, 15, 17, 18, 24, 28 }, 1492601,
    Table1[ProductID]
        IN { 4, 5, 6, 7, 8, 9, 11 }
        && Table1[OpportunityRecordTypeID] = 2
        && Table1[StageID] IN { 2, 13, 14, 16, 19, 29 }, 8857013,
    [etc.]
)

[Anonymous]

Hey @AlexisOlson, I did try this and came to know that Switch works on measures but not table columns. 

[AlexisOlson]

Eh? I'd expect what I wrote to work as a calculated column but not as a measure since it references row values rather than column aggregates. Switch can work with either but the rest of the syntax needs to be right.

[Anonymous]

Yes @AlexisOlson , syntax is the thing i'm struggling with.

[smpa01]

@Anonymous  Can you provide some sample data in a table form which is not image

[Anonymous]

Hey @smpa01 , adding 20 rows of data.

OpportunityID	OpportunityRecordTypeID	OwnerRoleID	ProductID	StageID	TypeID	ForecastCategoryID	AccountID	CategoryID	LostReasonID
1	1	4	1	5	8	7	1	1	1
1	1	7	1	5	8	7	1	1	1
1	1	7	1	14	8	7	1	1	1
1	1	7	1	16	8	7	1	1	1
1	1	7	1	2	8	8	1	1	1
1	1	4	1	16	8	8	1	1	1
1	1	7	1	16	8	8	1	1	1
1	1	7	1	16	9	8	1	1	1
1	1	7	1	16	3	7	1	1	1
1	1	7	1	21	3	7	1	1	1
1	1	7	1	2	3	8	1	1	1
1	1	7	1	16	3	8	1	1	1
1	1	7	1	22	4	5	1	1	1
1	1	7	1	3	4	7	1	1	1
1	1	7	1	4	4	7	1	1	1
1	1	7	1	5	4	7	1	1	1
1	1	8	1	7	5	2	1	1	1
1	1	4	1	6	5	4	1	1	1
1	1	8	1	6	5	4	1	1	1
1	1	8	1	8	5	5	1	1	1

[smpa01]

@Anonymous  looked into it and Looked into @AlexisOlson's solution as well. That solution is good.

[Anonymous]

Thank you @smpa01 and @AlexisOlson for your time on this, I am working on implementing this solution you mentioned and evaluate the outcome.

can i ask one more query, Is there a way to assign values to Switch DAX using Dimension tables. like 

SWITCH(TRUE(),'DBO DIMProduct'[ProductName] IN {'OutputServices','Realtime'} && 'DBO DIMOpportunityRecordType'[OpportunityName] = "New Client", 1234,....

[v-kelly-msft]

Hi  @Anonymous ,

If you have 2 dimension tables,it depends on the relationships between the 3 tables.

Could you share your .pbix file for test?

Remember to remove the confidential information.

Best Regards,
Kelly

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

[AlexisOlson]

You need to specify columns rather than tables. For example,

'DBO DIMProduct'[ProductName] IN VALUES ( 'OutputServices'[ProductName] )
    || 'DBO DIMProduct'[ProductName] IN VALUES ( 'Realtime'[ProductName] )