Counting 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??
How to Using Logical Function in DAX and Display the Result with Actual Value
Hi Frnds, I Created a Measure with existing tables and created a final result. In another Matrix Visual I would like to display the Final Result value which meets the Logical Function of Greater then 0 and Not Required if the Value fields which is less then or equal to 0. Pending Dispatch = SUM(salesproj[projqty])-SUM('Stock and Sales'[Total Sale Qty])-SUM('Stock and Sales'[Total Stock Qty])>0 with this Condition I am able to See the Result as below. But I don't want True or False as result. Please suggest me a DAX Measure.Sum of Count(column) > 2 in a measure
Hello to all, As you see in the above snapshot - Card show the overall count of Person (Names are repeated), I'm trying to achieve the total count of person with person repeating count is greater than or equal to 2. Based on filter it easy to get the value in the table, I need help in getting this value in a measure. The data is sensitive hence I have purposely hidden the names in the table. Greg_Deckler amitchandak Shaurya Ashish_Mathur
