partitions

3 Topics

Count of Locations with Sum of Values between min-max values
Hope I can describe this problem correctly! I have 2 tables, not joined to each other: FACT Table Location Category A Category B Value 1 A Z 51 1 B X 100 2 A Z 56 2 C Y 105 3 B W 61 3 D Q 110 4 A Q 52 4 A Z 102 Min/Max Table: Min Max 150 160 160 170 170 180 ...continuing pattern ...with each table having many, many more rows. What I am trying to do is come up with a DAX formula that will allow me to build a table visual with the following output: Min Max Count of Locations with Sum of [Values] between min/max 150 160 2 160 170 1 170 180 1 The DAX I have tried for the "Count of Locations..." column is this: Count of Locations = var minV = MIN(GroupsTbl[Min]) var maxV = MAX(GroupsTbl[Max]) var sumValues = SUM(FactTbl[Value]) var locCount = CALCULATE(DISTINCTCOUNT(FactTbl[Location]),FILTER(FactTbl,sumValues>=minV),FILTER(FactTbl,sumValues<maxV)) return locCount ...but this returns an output that is not paritioned by the Location column. Something like this using the example above: Min Max Count of Locations with Sum of Values between min/max 640 650 4 This would be pretty easy to solve by pre-aggregating the table to remove the 2 "Category" columns - but I need to be able to filter with slicers on those columns, and have the count of locations (and sum of values) update based on the selected values from the slicers. I hope this is enough explanation - apologies if not! Happy to provide more info/answer questions! 🙂 Any help would be greatly appreciated. Thanks -Joe
Solved
JoeCochran
1 year ago Place DAX Commands and Tips
633Views
0likes
3Comments
Azure partition refresh issue
Hi all, I am refreshing ds partitions via Azur runbooks and powershell scripts. This week I noticed that the error below appears (previously everything worked fine) This is the line that its calling: $Srv.Connect($connectionString) And the connection string: $connectionString ="Provider=MSOLAP;Data Source=$XmlaEndpoint;User ID=app:$PowerBIServiceApplicationID;Password=$PowerBIServiceApplicationKey;Persist Security Info=True;Impersonation Level=Impersonate" Do you have any suggestions why suddenly this script stoped working a week ago? Thanks in advance!
Misq
2 years ago Place Report Server
436Views
0likes
0Comments
Ranking using partition from relationship
I have a table of item details, and a table of events, which are either good or bad (1 or 0). There is a one-to-many relationship between 'Details'[Item ID] and 'Events'[Item ID] I'm trying to write measures that will rank each Item ID by Sum(Good/Bad), partitioned by the Item Owner, and an overall ranking. Any filters on the visual/page should affect the ranking. Details Item ID Owner 101 Dave 102 Jeff 103 Jeff 104 Jeff 105 Dave 106 Jeff Events: Item ID Good/Bad 101 1 101 0 101 1 101 0 102 1 102 1 103 0 103 0 103 0 103 0 104 1 104 0 104 0 104 0 105 1 Desired Result: Item ID Owner Count Good Rank Overall Rank by Owner 101 Dave 4 2 1 1 102 Jeff 2 2 1 1 103 Jeff 4 0 5 3 104 Jeff 4 1 3 2 105 Dave 1 1 3 2
Solved
Anonymous
3 years ago Place DAX Commands and Tips
518Views
0likes
1Comment