Forum Discussion
Create Measure from Fact Table
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...
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.]
)Hey AlexisOlson, I did try this and came to know that Switch works on measures but not table columns.
Anonymous Can you provide some sample data in a table form which is not image
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
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.
Yes AlexisOlson , syntax is the thing i'm struggling with.
