User Profile
DarrenLau
Advocate I
Joined 7 years ago
User Widgets
Contributions
Re: Problem with Using SWITCH Function
Hi, Found your question interesting and I came up with the following. Hope it helps and at the very least, points you to the right direction to get your formula working. Assign Status = VAR _checkAgreement = IF ( MAX ( Purchases[AgreementID] ) = "-", TRUE(),FALSE()) VAR _checkCreated = OR ( MAX (Purchases[CreatedBy])="BATCHJOB", MAX(Purchases[CreatedBy])="UC4BATCH") VAR _checkDocType = OR ( MAX (Purchases[DocumentType])="Z1", MAX(Purchases[DocumentType])="ZB") VAR _status = SWITCH(TRUE(), _checkAgreement && _checkCreated && _checkDocType, "SPA", _checkAgreement = FALSE() && _checkCreated, "Auto Calloff", _checkAgreement = FALSE() && _checkCreated = FALSE(),"Buyer Intervention", "Spot PO" ) RETURN _status Link to example PBIX for reference - SWITCH example I had some trouble with the ISBLANK() function so I substituted "-" into the data as a check to save time. Example table and results to check logic:-Re: Changing number format in a table
Number, date and other formats are managed at the column level of the table. Select the Order Amount column in your table and you should see the Column tools sub-menu shown below:- Table/Visual level controls - shortening to $Ks, $Ms and number of decimals to show.Re: Filtering a slicer (no relationship_
Thanks Jihwan_Kim That would be an acceptable solution if I am trying to find salespeople with sales, the slicer should show users filtered by location. I found the solution in another post and I have updated the PBIX - Filtering a slicer example DAX used Location Filters Users = IF ( MAX ( 'Salespeople'[Location] ) IN VALUES ( 'Sales Location'[SalesLocation] ), 1, 0 )Re: Performance Issues when Accessing Dynamics CRM Data
I use XRM Toolbox (XRM Toolbox link) to generate FetchXML queries to extract data from Dynamics 365 as I can use filters to restrict the number of records extracted (eg date after xx/xx/xxxx and in region AAA only). However, FetchXML queries will also get slow if all-attributes is used to query column information. By default, all-attributes will be used if more than 17 columns are requested. Some workaround and manual updating of the FetchXML query will be needed if you need to extract more than 17 columns in one query, but there is a very tangible 3-4x improvement in refresh time by not using all-attributes. XRM Toolbox (Power Query (M) Builder to be more exact) presents the tables and columns in a much easier to navigate interface compared to the built-in connector, and allows you to quickly find and select the columns you want. Also, if you have a predefined report/view on Dynamics 365, Power Query (M) Builder can use that view to generate FetchXML to extract the same columns and also combine the filters used to generate the view - definitely a time-saver.Help with calculating Min/Max values for Chart
Hi, I am very puzzled by this behaviour and want to seek community members advise. I am trying to determine Min/Max values of a chart using the DAX formula below:- MaxOppWonValue_MthName = MAXX ( ALLSELECTED ( 'Calendar'[Mth] ), [OppWonValue] ) MinOppWonValue_MthName = MINX ( ALLSELECTED ( 'Calendar'[Mth] ), [OppWonValue] ) where [OppWonValue] is OppWonValue = CALCULATE ( SUM ( 'Opportunities'[Revenue] ), 'Opportunities'[Status] = "Won" ) The issue is when I use the Mth column in Calendar table (eg Jan, Feb, Mar, Apr ...) as the common axis on the chart, the charts do not show the correct Min/Max values. However, if I change the DAX formulas to MonthNumberOfYear and the chart axis to the MonthNumberOfYear, the calculations work. MaxOppWonValue_MthNumber = MAXX ( ALLSELECTED ( 'Calendar'[MonthNumberOfCalendarYear] ), [OppWonValue] ) MinOppWonValue_MthNumber = MINX ( ALLSELECTED ( 'Calendar'[MonthNumberOfCalendarYear] ), [OppWonValue] ) What am I doing wrong or missing here?DAX measure - ignore year and quarter slicers
Hi, Need some guidance to create measure(s) which can be broken down into two steps/parts. Problem statement - need to calculate prior quarter's opportunities actual close amount by month based on the actual close dates. Opportunities table has an active relationship on Estimated Close Date to Calendar table, and a secondary relationship on Actual Close Date. Created Measures Opps Actual Close = calculate(sum(Opportunities[Actual Revenue]),USERELATIONSHIP(Opportunities[Actual Close Date],'Calendar'[Date])) The measure above works with the selected slicers (Year = 2020; Quarter = Q1). I want to determine the actual close revenue by month for previous quarter, and the first step is to find out the 3 month values preceding the current quarter (in this example, 10,11,12; would change to 1,2,3 in 2020 Q2) Second step is to use the month values to sum up the previous quarter's actual close revenue. Will need 3 different measures as there are follow-on calculations that need to be done, eg:- PrevQtrFirst Month PrevQtrSecondMonth PrevQtrThirdMonth
