Dynamic DAX Measure to Count Distinct Rows filtered by separate query
Hi All, I'm pretty new to DAX and struggling to figure this out so any help is greatly appreciated. I have the following data model (simplified): I am trying to write a measure that runs a distinct count on the number of students ('Enrolment'[Student ID]) that have attended an appointment ('Appointments'[Attendance Status]=2). I then want to be able to slice on this measure by date, dimensions etc. The following is currently giving me the correct total over all my data: # Appointment Attendees (Distinct Students) = CALCULATE ( DISTINCTCOUNT('Enrolment'[Student ID]), FILTER(ALL('Appointments'), 'Appointments'[Attendance Status] = 2) However I have a number of other slicers in my report, drawing on values from fact and dim tables. These slicers are not working with the measure in the report , it only displays the original value. Is there a way to re-write the measure so it still works dynamically with slicers? Thanks in advance for your help.
Handling multiple Fact Tables with different Aggregation
Dear Community, I have 2 dim and 2 fact tables with the following model: So the problem I get is in vizulising both fact values in one Visual without grouping (don't want to MAX or SUM the BookingDate or costs). Here the Name "abc" should get 30 sales and three different costs and the other Names ("def" and "ghi") should get 30 and 15 sales and no costs (tables on the right). But as you can see the Visual showing all the values of both fact tables without any relationship (table in the middle): I would expect this: Does somebody has any Idea? Thanks in advance!
SUMX over multiple tables
Hello, I am relatively new to Power BI and don't know how to solve the following. Maybe someone knows the answer and could help, that would be great 🙂 I have 3 tables (see picture) (meanwhile I uploaded the file here: https://drive.google.com/drive/folders/181jeOfllqmWO1VIIQ8lMpJ0iezT9AgaQ?usp=sharing) The first table lists sales articles with quantities in kg per month. Each of these articles is produced out of different material components. The respective material and it's share is listed in a second table. Now each of these raw materials has different purchase prices per months, this is listed in a third table. I would now like to show the sum of material costs per sales article per month (orange column). I don't want to add a new calculated column tho, I would like to have this calculation completely inside a measure. Unfortunately I was not able to create a working DAX formula. The formula should lookup the respective material components and it's share and multiply it with the respective purchase price with consideration of the month. Also it would be nice if this still works in combination with a time slicer. Thank youuuuu! Cheers 🙂Create column based on difference between two dates from two tables
Hi, I am trying to create a Calculated column to track the difference between two dates from two different columns. The goal is to find when a shipment has been recieved (Final Merger) and when the product has been recieved and put away (Export Worksheet). I am trying to create a calcuated column using an if statement, however i run into the error. The If statement is created within Export OR Final Merger 1. Wont allow me to select multiple tables in my If Statement. If I select either fields, I wont be able to select the other table. 2. Error - "Cannot Find (Column Name) Tables Table Column Type Final_Merger First Date Date Export Worksheet Second Date Date Date Dimension Date Date References First Table Second Table Column Relationship Final_Merger Export Worksheet Receipt ID Many to Many Final Merger Date Dimension Date Many to One
Measure that divides a value from another table, with a sum from another table
Hello, I have a problem which I'm having problems getting my head around, which I am hoping for any inputs on. I have two tables. One date table with available hours for a given month, and another table containing "executed hours" for a given month per employee and performed task. See examples below. Executed hours Date table Wanted output The wanted output is a table that show the number of full time employees for a given month. If any employee has worked over the avaible hours for a respective month it rounded down to 1, whilst if exected hours is below available hours I want the fraction (as shown for employee 2 in januar). I have managed all the numbers right, apart from the totals (totalsum) which still eludes. Hopefully this makes sense. Thank you for your time!Calculate Measure across multiple FactTables in a Data model
Hi, New to PBI and really enjoying it. Am running into an issue that I'm sure should be do-able with DAX measures. I am looking to calculate number of stores that have purchased a given item. See attached picture of my data model. My PBIX file can be downloaded from here: https://drive.google.com/file/d/1OS02Wb-M8OGlhTTNHwEqUPKfTY82Wl5i/view?usp=sharing Here's a brief description: ProdGrp: is grouping of items and categories. It's linked through item to: SalesData: that shows what item was shipped/sold to what store and associated Revenue. Store_Profiles: is linked to SalesData and connects store attributes given in StoreFronts: This has the total count of store outlets per year. This has to be averaged for a given store over a period. For example if both years 2017 and 2018 are selected the outlet sum for Store A should be the average of the sum of outlets in 2017 and sum of outlets in 2018 (this measure is working for me I believe) My goal is: 1. a measure that looks at item or category of Product and shows the sum of outlets for the store that purchased that item (or category). For exampe, in 2017, TV was only sold to store A. And Store A has total of 39 outlets. So, for 2017 the TV item should show the store outlets count should be 39. In 2018 only Store B and Store C purchased TV (total quantity 148). As their outlet sum is 86, that's the value i'd like to show. This measure has to work on the ProdGrp table as I need to show the same for Categories (Electronics, Appliances etc.) 2. Would be great if the above measure also allows to have a cutoff to ignore sales quantities below a given % (say 10%). So, for 2018 we see that Store C only purchased 3 TVs. Since it's below 10%, we ignore the store outlet count for C and only now show 10 store counts (instead of 21). So, the store names need to be ranked and only top 90-95% of the names need to be passed on to the measure for adding the outlets. 3. A second measure that shows the top n stores that purchased the item/category. Here I have only 3 stores but in my actual data i have hundreds of consuming categories so would like to show top 3 (or n) My PIBIX file can be downloaded from here, that shows a few of my existing measures https://drive.google.com/file/d/1OS02Wb-M8OGlhTTNHwEqUPKfTY82Wl5i/view?usp=sharing The challenge (as I read) is that StoreFronts and SalesData are two FactTables. ProdGrp is a dimension table that is not linked to StoreFronts table directly. However, I am hoping Dax should be able to pass on the Store IDs from SalesData table to StoreFronts table and use those IDs to calculate the sum of outlets. Greatly appreciate any help, hints and suggestions!
