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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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