Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 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.

 

 

 

Need Help

10 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

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

      • smpa01's avatar
        smpa01
        Community Champion
        4 years ago

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