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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors