Cannot expand next hierarchy in Matrix
I found an issue in a visual in Power BI service. I have a hierarchy of Rows: Month -> Week -> FltDate. But I cannot expand to the level of FltDate. And when I try fixing it in edit mode, removing and re-inserting FltDate to the Rows, it works. But when I save the report and reopen it in view mode, everything remains, it cannot be expandedCounting Rows in Different Table based on Current Row Context
Hey all, First of all, thank you for taking your time to stop by and reading my help request! I am an intermediate user of Power BI and have started to encounter difficulties with more complex logic with DAX. Task: I would like to calculate the % of individuals in each branch that have completed the training course. To do that, I need to divide the # users completed by the # users allocated for training. Problem: I cannot figure out the DAX code to find # users allocated for training that respects the row context in the matrix while looking at a different table that is related 2 steps away. For example, lets say the first row calculation for Adelaide - Expert - Cert 2 If i could write it in pseudocode, it'd be something like this: COUNTIF( USER[Location] = "Adelaide" && USER[ALLOCATED] = "Cert 2") FROM All Allocated Users However, the confusing part for me is that the relationships are not defined clearly. I have a table with every user, and then another table allocating users to a MAKE Then, I have a historical table for all training performed and another table explaining the structure of the training curriculum. This also has a MAKE field. The image below is a snippet of the table, the last column is simply a Count of all the User IDS from the ALLOCATED USERS table and is returning the full list for every row in the table. I have tried many other DAX codes but cannot get it to simply count the number of users from the ALLOCATED USERS table. Datastructure: Allocated Users Curriculum Training History Link to PBIx: (updated link) https://www.dropbox.com/scl/fi/o3mjeaqyyukpb402lqhk7/PBI_Help_Counting-Users.pbix?rlkey=w52n2x3re2r0b5em6fb1g7fr3&st=2ngf8u85&dl=0 All help is sincerely appreciated and I will try my best to reciprocate in the community when I get better myself with Power BI. Thank you all again! Regards, Namdu
DAX in building Rolling 4 Week Average
Hi, I need help in building 4 week rolling average for "Net Change in Price and Cost" with below sample data Change in Price, Change in Cost and Net Change in Price and Cost are all 3 measures. Net Change in Price and Cost = Change in Price - Change in Cost I do have separate date table and the above data shows weekly data and my report has Invoice Date as Filter. I need to calculate rolling 4 week average, here the tweak is that for first week start date 12/07 rolling average should be same while for next 12/14 it should (-4.80% + 0.18%)/2 = -2.31% for 12/21 it should be (-4.80% + 0.18% - 0.04%)/3 = -1.55% for 12/28 it should be (-4.80% + 0.18% - 0.04% -0.65%)/4 = -1.33% for 01/04 it should be (0.18% - 0.04% -0.65% +1.03%)/4 = 0.13% as shown below I have created below DAX but its not working as expected Net Change in Price and Cost 4 Wk Rolling Avg = var start_day = MIN('Date'[Week Start Date])-21 var end_day = MAX('Date'[Week Start Date]) return CALCULATE([Net Change in Price and Cost], DATESBETWEEN('Date'[Week Start Date],start_day,end_day), REMOVEFILTERS('Date'[Week Start Date]))/CALCULATE(DISTINCTCOUNT('Date'[Week Start Date]),DATESBETWEEN('Date'[Week Start Date],start_day,end_day),REMOVEFILTERS('Date'[Week Start Date])) Can someone help me on this??
