live connection

4 Topics

DAX measure: ID from MIN date - historical data in live connection
Hello all, This is a follow-up question from my last post: DAX measure: MIN date for historical data in live connection I have a live connection to a SQL table “Data” with historical data. Example Data: ID Status Start End Changed 1 A 2022-05-13 2022-05-13 1 B 2022-05-13 2022-05-14 2 A 2022-05-15 2022-05-15 3 A 2022-05-15 2022-05-15 2 C 2022-05-15 2022-05-16 2022-05-16 3 B 2022-05-15 2022-05-16 3 B 2022-05-15 2022-05-17 3 C 2022-05-15 2022-05-18 2022-05-18 4 A 2022-05-15 2022-05-15 4 A 2022-05-15 2022-05-16 5 A 2022-05-15 2022-05-16 6 A 2022-05-16 2022-05-16 I get the MIN Start-date with Status “A” if the corresponding ID had no other Status since then according to the following formula (all thanks to Jihwan_Kim): VAR _IDunderA = SUMMARIZE ( FILTER ( Data, Data[Status] = "A" ), Data[ID] ) VAR _IDunderothers = SUMMARIZE ( FILTER ( Data, Data[Status] <> "A" ), Data[ID] ) VAR _IDonlyA = EXCEPT ( _IDunderA, _IDunderothers ) VAR _newtable = CALCULATETABLE ( Data, TREATAS ( _IDonlyA, Data[ID] ) ) RETURN MINX ( _newtable, Data[Start] ) Result for example data: 2022-05-15 What to achieve: Now, I would like to get the corresponding ID or IDs to the MIN Start-date respectively. Expected Result: ID 4 ID 5 I tried a combination of FILTER and SELECTEDVALUE but it did not work so far. VAR _IDunderA = SUMMARIZE ( FILTER ( Data, Data[Status] = "A" ), Data[ID] ) VAR _IDunderothers = SUMMARIZE ( FILTER ( Data, Data[Status] <> "A" ), Data[ID] ) VAR _IDonlyA = EXCEPT ( _IDunderA, _IDunderothers ) VAR _newtable = CALCULATETABLE ( Data, TREATAS ( _IDonlyA, Data[ID] ) ) RETURN CALCULATE(SELECTEDVALUE(Data[ID]), FILTER(Data,Data[Start]=MINX( _newtable, Data[Start]))) Any suggestions? Thanks!
Solved
Rsquare
3 years ago Place DAX Commands and Tips
587Views
0likes
1Comment
DAX measure: MIN date for historical data in live connection
Hello all, I have a live connection to a SQL table “table1” with historical data. Example Data: ID Status Start End Changed 1 A 2022-05-13 2022-05-13 1 B 2022-05-13 2022-05-14 2 A 2022-05-15 2022-05-15 3 A 2022-05-15 2022-05-15 2 C 2022-05-15 2022-05-16 2022-05-16 3 B 2022-05-15 2022-05-16 3 B 2022-05-15 2022-05-17 3 C 2022-05-15 2022-05-18 2022-05-18 4 A 2022-05-15 2022-05-15 4 A 2022-05-15 2022-05-16 5 A 2022-05-16 2022-05-16 What to achieve: Now, I would like to get the MIN Start-date with Status “A” if the corresponding ID had no other Status since then. Expected Result: The output regarding the example table would be 2022-05-15. Solution: ID 4 and ID 5 are the only IDs that did not switch their state. ID 4 provides an earlier start date then ID 5. I tried different approaches, like comparing a subset of Status A with a subset of other statuses to get the IDs that are in subset A but not in the other one. Another means could be an index if it would be possible to calculate a column. However, since it is a live connection, I cannot calculate new tables or columns. The DAX measure has to be calculated without those intermediate steps. A change of the connection type is not possible. I appreciate your help! Thanks.
Solved
Rsquare
3 years ago Place DAX Commands and Tips
1.1KViews
0likes
2Comments
Measure in Live Connection - Incorrect Total
Hello, I know there have been many solutions on incorrect totals from measures in Live Connection, but I can't seem to figure out the solution for my measure: No of Sessions = VAR _encounters = CALCULATE ( [Amount], FILTER ( ALL ( 'Profitability Account' ), 'Profitability Account'[Description] = "Encounters" ) ) VAR _NoOfDays = Calculate(DISTINCTCOUNT('Time'[CalendarDate]),Filter ('Time', 'Time'[DayOfWeekdayDescription] <> "Sunday" && 'Time'[DayOfWeekdayDescription] <> "Saturday" )) VAR _NoOfSession = _NoOfDays * SWITCH ( TRUE (), _encounters <= 2, 0, _encounters >= 2 && _encounters <= 10, 1, _encounters > 10, 2 ) RETURN _NoOfSession This gives me the following result (I have covered up the practice locations): You can see the No of Sessions total is incorrect. I even attempted something different by trying something different. I wanted to try something like DISTINCTCOUNTX (but this does not exist in DAX) - so I found the below solution: No of Sessions New = VAR _NoOfDays = COUNTROWS( DISTINCT( SELECTCOLUMNS( 'Time', "CalendarDate",'Time'[DayOfWeekdayDescription] <> "Sunday" && 'Time'[DayOfWeekdayDescription] <> "Saturday" ))) VAR _NoOfSession = _NoOfDays * IF ( [Encounters] <= 2, 0, IF([Encounters] >= 2 && [Encounters] <= 10, 1, 2 )) RETURN _NoOfSession I replaced the VAR _encounters with a measure instead [encounters] . And I replaced the SWITCH statement for an IF statement instead to see if that will do anything different, but it still doesn't work: The following is the Encounters measure (it's exactly the same as in the original No of Sessions DAX: Encounters = CALCULATE ( [Amount], FILTER ( ALL ( 'Profitability Account' ), 'Profitability Account'[Description] = "Encounters" )) Please help me write a new DAX measure that will fix this issue and give me correct Totals 🙂
Solved
sabilahmed
3 years ago Place DAX Commands and Tips
742Views
0likes
3Comments
PBI Live connect to AAS (dynamic columns in matrix) ?
Hi all, Iam in trouble with creating matrix in PBI which will dynamically change columns/rows based on selection from slicer. Iam using live connect to Azure analysis tabular model actually and trying to do following solution: static data table for colum names, measure to identify change, row_selector:=sum('Value selector row'[Order]) calculated column "rows" = var x1=IF([row_selector]>39,1,[row_selector]) var x2= CALCULATE(VALUES('Value selector row'[Name]),FILTER('Value selector row','Value selector row'[Order]=x1)) return switch( x2, "Stredisko",RELATED('xy1'[x)]), "Prevádzka",RELATED('xy2'[y)]), "Pokladňa",RELATED('xy3'[z]), "N/A" ) In AAS visual studio is everything working fine, but after deploy to PBI row value is not changing either when underlying measure changes correctly. Is there some way to refresh calculated colum when PBI (live connected) slicer selection changes ? Thank you
dforro
5 years ago Place DAX Commands and Tips
1KViews
0likes
1Comment