Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
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.
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 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
@Anonymous looked into it and Looked into @AlexisOlson's solution as well. That solution is good.
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] )
User | Count |
---|---|
84 | |
77 | |
69 | |
46 | |
41 |
User | Count |
---|---|
106 | |
50 | |
49 | |
40 | |
39 |