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
Calculate the number of unique values in a column and no value in another column
Hi Everyone! Asking for your help in DAX. How can I calculate the number of departments with no FTE value but also check that no projects have no FTE value? So my desired result is 1 because Department 2 has no value in Project B and Project A. There's only 1 department that has no FTE value from all of the projects. Basically, I want to check how many unique departments that have no FTE value across all projects. Thank you. - Third
How to make dropdown slicer present "multiple selections" if more than one value has been selected?
Hello everyone, In order to solve the problem I had in the 1st screenshot I applied the dax code shown in the 2nd screenshot. The code seems to be working perfectly fine as long as the user has chosen one specific deparment (single selection), the output is shown in screenshot 3. However in the case where a user picks a second deparment (screenshot 4), the measure will continue to show only one of the two or multiple selections. Is there a way to tweak the code (or a completely different code), that could instead show "Multiple selections" (Screenshot 5) when the user has chosen more than one departments? 1st 2nd 3rd 4th 5th Thank you in advance!
Display last 5 weeks based on Selected Date
Hi everyone, I am pretty new to Power Bi and DAX, so I'd appreciate any pointers here. Here is my problem: Based on the selected date, I need to display data in a matrix table on the selected date and 5 weeks ago. Unfortunately, this is a problem from work, so I'm unable to share the actual data but here is an example with some dummy data: My selected date is 9/15/23. This is the data that should be displayed: If I select the date to be 9/8/23, this is how the new selected data should be displayed: In essence, selecting 9/8/23 results in the 9/8/23 totals to be added to the 9/15/23 totals. The remaining data results would display weekly from 8/11/23 up to 9/1/23. This type of functionality should exist for any selected date I choose. Here is a dummy view of what my actual table looks like. I also have a independent date table that is the distinct updated date from my actual table. Updated Date Days Open Status Count Date Difference (Today - Updated Date) 9/15/23 0 to 9 A 3 6 9/15/23 10 to 19 A 13 6 9/15/23 0 to 9 B 5 6 9/15/23 10 to 19 B 10 6 9/8/23 0 to 9 A 5 13 9/8/23 10 to 19 A 3 13 9/8/23 0 to 9 B 6 13 9/8/23 10 to 19 B 5 13 Currently, I have a display similar to the 1st picture, but this is without selecting any dates. I calculated a date difference between the updated date and Today's date and used those numbers as a filter to help display something similar to the first picture. Now if I try to select a date using a filter, it will only display that selected date's data, which isn't what I want. I tried to utilize the independent date table in the filter but obviously was running into issues because there's no actual relationship between my actual table and the date table. And even if I'm able to figure out this issue, I'm not even sure how I can visualize the second picture. Is this type of visualization even possible in Power BI? I just don't know where to begin 😞
