analysis services

4 Topics

Erroneous Results from COUNTROWS() in Context-transition
Unless I'm missing something, I'm pretty sure this isn't supposed to happen: EVALUATE CALCULATETABLE ( CALCULATETABLE ( ADDCOLUMNS ( VALUES ( Users[User ID] ), "Test Expression 1 - CORRECT", CALCULATE ( CONCATENATEX ( FILTER ( VALUES ( 'Privilege Conflicts'[Privilege 2 ID] ), 'Privilege Conflicts'[Privilege 2 ID] IN VALUES ( Privileges[Privilege ID] ) ), 'Privilege Conflicts'[Privilege 2 ID], " | " ) ), "Test Expression 2 - INCORRECT", CALCULATE ( COUNTROWS ( FILTER ( VALUES ( 'Privilege Conflicts'[Privilege 2 ID] ), 'Privilege Conflicts'[Privilege 2 ID] IN VALUES ( Privileges[Privilege ID] ) ) ) ) ), CROSSFILTER ( 'Users and Privileges'[Privilege ID], Privileges[Privilege ID], BOTH ) ), Users[User ID] IN { 102, 103 } ) Results: Notice how, for User ID = 102, the results in the test expressions are inconsistent, despite the fact that they are semantically basically the same. For reference, note that the following produces expected results: EVALUATE CALCULATETABLE( CALCULATETABLE( { ( -- Test Expression 1 - CORRECT CALCULATE( CONCATENATEX( FILTER( VALUES( 'Privilege Conflicts'[Privilege 2 ID] ), 'Privilege Conflicts'[Privilege 2 ID] IN VALUES( Privileges[Privilege ID] ) ), 'Privilege Conflicts'[Privilege 2 ID], " | " ), Users[User ID] = 102 ), -- Test Expression 2 - CORRECT (inconsistent with prior query) CALCULATE( COUNTROWS( FILTER( VALUES( 'Privilege Conflicts'[Privilege 2 ID] ), 'Privilege Conflicts'[Privilege 2 ID] IN VALUES( Privileges[Privilege ID] ) ) ), Users[User ID] = 102 ) ) }, CROSSFILTER ( 'Users and Privileges'[Privilege ID], Privileges[Privilege ID], BOTH ) ), Users[User ID] IN { 102, 103 } ) Results: --- It appears that something might not be functioning correctly between the context-transition and COUNTROWS(), but not fully sure. marcorusso Jeffreywang --- Power BI version: 2.139.2054.0 Data model:
djskro
1 year ago Place DAX Commands and Tips
567Views
0likes
2Comments
Creating a Slicer based on Range of Measure values - SSAS
Hi! I have created a Measure called [OverallBlendedScore]: (( [AveragePatientDTPScoreRank] * SELECTEDVALUE ( 'SliderDTP'[DTPWeight] ) ) + ( [AveragePatientCoreConditionCountRank] * SELECTEDVALUE ( 'SliderMedCondition'[MedConditionWeight] ) ) + ( [AverageSVIOverall] * SELECTEDVALUE ( 'SliderSVI'[SVIWeight] ) ) + ( [AveragePatientDrugCountRank] * SELECTEDVALUE ( 'SliderDrugCount'[DrugCountWeight] ) )) / [TotalSelectedWeights] I want to know how to create the range of values in [OverallBlendedScore] as a Slicer. The range of expected values is 0-100. I am using Analysis Services tabular model and Tabular Editor. I feel like I should create a new calculated table that provides the values, but not too sure. Any help is appreciated!
adam12237
3 years ago Place DAX Commands and Tips
585Views
0likes
1Comment
DAX Measures in SSAS Tabular Model or PBIX Report File?
Hello, I tend to test DAX measures in a local report file and then graduate them to the centralized SSAS model for organization, ease of administration and re-use in other reports. The number of DAX measures in the model is growing quite quickly. One reason is that we do not have access to Calculation Groups yet (we are on SQL Server 2016), but also because I am keeping all DAX measures centralized in the model. How do you decide where to input your DAX measures, in the model or only in the PBIX report file? Thank you for your help.
Solved
blynchdata
5 years ago Place DAX Commands and Tips
1.5KViews
0likes
2Comments
ALL (Column) behavior issue
I am working on some DAX and it is not working as I expect. I tried using some of the DAX functions to do this, but they weren't quite what I was needing. Also, I am doing this in SSAS which seems to have a very slight difference in support for newer DAX commands. I am working on creating a Prior Year To Date functionality, but the precanned TOTALYTD doesn't display results when certain filters are applied. Instead, I am trying to use the code below. The main problem I am having is I need to return all of the results from the Sales table with filters still there except for on the date fields as I want to apply a custom date filter as shown in the code below. Sales Amount PYTD:= RETURN SUMX( FILTER( ALL(Sales[invoiced_date]), YEAR('Sales'[invoiced_date]) = SELECTEDVALUE('- Date'[YearOrder]) - 1 && MONTH('Sales'[invoiced_date]) <= SELECTEDVALUE('- Date'[MonthOfYearNumber]) ), [sales_amount] ) I am using ALL(Sales[invoiced_date]) which according to the Microsoft documentation here, this code should "Removes all filters from the specified columns in the table; all other filters on other columns in the table still apply. All column arguments must come from the same table. The ALL(Column) variant is useful when you want to remove the context filters for one or more specific columns and to keep all other context filters." I interpret that as by using this in the manner I have, it should return all data in that table but remove the filter from the Sales[invoiced_date] column. The problem is, when I do this, Visual Studio is throwing a "Semantice Error: Column 'sales_amount' cannot be found or may not be used in this expression." Can anyone give me some insight on what I am doing wrong here? Thank you in advance.
Solved
KevinW_SDP
6 years ago Place DAX Commands and Tips
2.2KViews
0likes
6Comments