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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

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:

Babulal_0-1633373217628.png

SalesTarget for few Product, OpportunityRecordType and Stage:

Babulal_4-1633374918662.png

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

 

Babulal_3-1633374163393.png

 

Or any other idea that can resolve the problem.

 

 

 

10 REPLIES 10
AlexisOlson
Super User
Super User

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
Not applicable

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

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
Not applicable

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

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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

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

 

smpa01_0-1633441529381.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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

 

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!

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

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

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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