Why does this measure work?
This is more of a "Why does this work" question rather than a "how do I" question. Here is some context of the scenario using dummy data: I have a fact table that looks like this: Each column ID points to its respective dimension table Within those dimension tables, the corresponding ID determines a value. For example the Risk Score Value column in the Risk Score dimension (Relationship = Risk Score_ID -> AnalysisCodeValueID) I have created two measures: 1. Risk Score SUM = CALCULATE(SUM('Risk Score'[Risk Score])) 2. Total Risk Score = CALCULATE(SUMX('Analysis Codes',RELATED('Risk Score'[Risk Score]))) When I bring these elements into a table visual why does the Measure: "Risk Score SUMXRelated" return the correct sum but the Risk Score SUM measure does not? Any help would be appreciatedTotal DISTINCTCOUNT() also for BLANKS() with SAMEPERIODLASTYEAR() calculations
Hello, I need help with the following DAX scenario. Data context: We buy items from vendors. Question: Comparison of previous year with total number of suppliers. Problem: Number of suppliers is "wrong" because only the selected year (2023) is calculated [DISTINCTCOUNT()], but I need the DISTINCTCOUNT() for all suppliers. Even if we didn't buy any of these in 2023. I need all of them from the previous year and the year before. (Description see screenshot) For 2023 > not #Vendors=3 > #Vendors=5 Any ideas on how to overcome this challenge? Thanks in advance! Data fact Vendor Date Amount Item A 15.01.2023 10 abc B 15.07.2023 5 abc C 15.08.2023 3 def D 15.01.2022 6 abc A 15.07.2022 4 abc A 15.08.2022 2 def B 20.01.2022 7 abc E 15.01.2021 6 abc A 15.07.2021 4 abc A 15.08.2021 2 def dimVendor Vendor VendorName A Aaa B Bbb C Ccc D Ddd E Eee calendar = FILTER ( CALENDARAUTO ( 3 ), YEAR ( [Date] ) >= 2020 ) dimItem Item ItemName abc AbeCe def DeEef
Looking to get a rolling 12 months on a measure column
I'm having the hardest time figure out the right DAX syntax to get what I'm looking for. I have a table visual that is pulling data from a SharePoint list. From this data, I need to calculate the %completed, %Incomplete, %Blank. I'm pretty sure I have those measures setup correctly. What I need to do now is get a rolling 12 months based on the %Completed measure. I've done the whole google search to see what I can find and I do find some entries that should be helping but what I'm not getting is the rolling 12 months. It's just returning the same information that's in my %Completed Column. I've created a Calendar table as well as my data table and I'm still not getting what I need. Any help to get this figured out would be very much appreciated.
COUNTAX DAX Formel in Power BI Desktop need explanation
Hello all. I have two Lookup tables. One of it has data about learning courses (number of course, Course Start data, the quantitiy of the participants etc). The second one is a person table with learning courses in which they have participated. The both of the tables are exported from Excelsheets. "Cousrse Groups Lookup " Table CS Course number Date of Start Date of End Course activity state Participants Quantitiy Participants successful Quantitiy KPI 1 04.09.2024 25.02.2024 ended 5 5 Not used now 2 27.03.2024 08.05.2024 ended 22 21 Not used now 3 15.05.2024 15.02.2025 active 6 0 Not used now "Person List Lookup " Table Personal Nr Persons CS Course number Participation state Country 1 Lionel 1 1 France 2 Laurence 1 1 France 3 François, Vetea 1 1 France 4 Sashko 1 1 France 5 Vetea 1 1 France 6 Schreiner 2 1 Germany 7 Schneider 2 1 Germany 8 Schindler 2 1 Germany 9 Backenbauer 2 1 Germany 10 Ivaschko 2 1 Germany 11 Lindnder 2 1 Germany 12 Musterman 2 1 Germany 13 Musterman1 2 1 Germany 14 Müller 2 1 Germany 15 Schmidt 2 1 Germany 16 Fischer 2 1 Germany 17 Weber 2 1 Germany 18 Meyer 2 1 Germany 19 Wagner 2 1 Germany 20 Becker 2 1 Germany 21 Schulz 2 1 Germany 22 Bauer 2 1 Germany 23 Schäfer 2 1 Germany 24 Koch 2 1 Germany 25 Schröder 2 1 Germany 26 Neumann 2 1 Germany 27 Zimmermann 2 2 Germany 28 Braun 3 3 Germany 29 Krüger 3 3 Germany 30 Hofmann 3 3 Germany 31 Klein 3 3 Germany 32 Schmitt 3 3 Germany 33 Bley 3 3 Germany 34 Schmitz 0 4 Germany 35 Krause 0 4 Germany 36 Schulze 0 4 Germany 37 Schuster 0 4 Germany 38 Köhler 0 4 Germany 39 König 0 4 Germany 40 Winter 0 4 Germany 41 Schach 0 4 Germany 42 Huber 0 4 Germany 43 Kaiser 0 4 Germany 44 Fuchs 0 4 Germany 45 Peters 0 4 Germany 46 Lang 0 4 Germany 47 Möller 0 4 Germany 48 Weiß 0 4 Germany 49 Jung 0 4 Germany 50 Hahn 0 4 Germany 51 Schubert 0 4 Germany 52 Vogel 0 4 Germany 53 Vogler 0 4 Germany 54 Keller 0 4 Germany 55 Berger 0 4 Germany 56 Winkler 0 4 Germany 57 Roth 0 4 Germany 58 Beck 0 4 Germany 59 Schumacher 0 4 Germany 60 Groß 0 4 Germany 61 Seidel 0 4 Germany 62 Ziegler 0 4 Germany 63 Kuhn 0 4 Germany 64 Bergmann 0 4 Germany 65 Pfeiffer 0 4 Germany 66 Novak 0 4 Czech Republic 67 Noak 0 4 Czech Republic 68 Pietsch 0 4 Czech Republic 69 Kowalski 0 4 Czech Republic (no worry, all names are used for example) In the "Course Groups Lookup" table i have the column "Participants Quantitiy" yet. But it is calculated and prompt manually. Every time if the new course begin, the neu column shall be added into the Table "Course Group Lookup" with recalculation, based on the data in the "Person List Lookup" Table. The both tables have one relationship 1 : x. See the figure below My goal is to add the column "Participants Quantitiy CC" with a measure, which just calculate the participants quantity for every learning course (see the figure). Of course i could use the DAX Formula CALCULATE with FILTER and logical conditions. But in this case i shall add the logic if a new learning course begin and this is not what i want. If you will see attantively on the measure itself, and on the result in the added column, probably you will come to the same conclusion as i - it doesn´t work because i have nearly 30 persons with the course number "0" and due to the logic of the formula COUNTAX i shall have for all of the learning courses 30 persons. But you will be wery surprised because it works (see the measure below and the content of the column in the picture above). EXAMPLE of Quantity = COUNTAX ( 'Person List Lookup', 'Person List Lookup'[CS Course number] = 0 ) There is no matter if i compare with 0 or change the condition to less as 2. The result will be the same - it works as expected. Truly says, i have no idea, but i will be appraciate to you to hear your explanations.
dax measures for calculating dates
Hello All I am trying to calculate remaining kms that are allowed for using rented car, for example nissan sunny started the rent on 9 oct 2023, and monthly allowance is 2500 km, so by february i have an allowance of 12500 km, so when i dont select year/months my calculation is giving wrong remaining kms But when am selecting months/year, everything is correct, as shown in snapshot, can anyone tell me what am doing wrong in dax measure
SQL Syntax Error in Power Bi while fetching Data From Diff Tables to Table - Visual
Hello Authors Please land me some help, I am getting Data from mysql server (Version 8.0) by MariaDB connector (in Direct Query) the schema is Star there and has 3 Tables and 2 connection between tables. Connection - FactTable - table2 (Many to many) FactTable - table3 (Many to One) when I try to put or see data in Table Visual I face this error when I put - Field1 from Table3 Field2 from FactTable What Should I do please Guied Thank You in Advance
lookup through multiple tables
Hi people, I hope you can help me with a challenging task in making a calculated table in Power BI: I have a data model loooking like this: My challenge is an effort to make a calculated table looking like this: my problem is, that in order to consolidate "Product Name" and "Customer Name" in the same table, I need to look them up through the Sales (Fact) table. I have made an attempt in the following DAX script, where I start out with the use of a SUMMARIZE() function to build a table on the "Product"-attributes. But I can't figure out how to add the "Customer"-attributes to the table, when the relation between the two goes thorough the "Sales"-table? This is my DAX code: Table = SUMMARIZE('D_Product','D_Product'[Product Key],'D_Product'[Product Name], "Customer_key", VAR RelatedValues_table_prod = FILTER( RELATEDTABLE('F_Sales'), VALUE('F_Sales'[PRODUCT KEY]) = VALUE(RELATED('Product'[PRODUCT_KEY])) ) VAR RelatedValues_table_cust = FILTER( RELATEDTABLE('F_Sales'), VALUE('F_Sales'[CUSTOMER KEY]) = VALUE(RELATED('D_Customer'[CUSTOMER KEY])) ) RETURN MAXX(RelatedValues_table_cust, 'F_Sales'[CUSTOMER_KEY]), "Product key", VAR RelatedValues_table_prod = FILTER( RELATEDTABLE('F_Sales'), VALUE('F_Sales'[PRODUCT KEY]) = VALUE(RELATED('D_Product'[PRODUCT_KEY])) ) VAR RelatedValues_table_cust = FILTER( RELATEDTABLE('KPI Sell In'), VALUE('KPI Sell In'[CUSTOMER_KEY]) = VALUE(RELATED('Customer'[CUSTOMER_KEY])) ) RETURN MAXX(RelatedValues_table_prod, 'KPI Sell In'[PRODUCT_KEY]) ) To be honest, I am not quite sure how to cóntinue from here? Can it be done? Any help or guidance is much appreciated. Thanks. Br, JayJay0306Summarize table with zero
Hello, I have two tables as below: Sales Salesperson Date Category AAA 2023-01-02 BAT BBB 2023-03-03 TGA AAA 2023-03-03 TGA BBB 2023-03-03 TGA CCC 2023-04-23 BAT CCC 2023-04-23 TGA Salespersons Name Dept AAA 01 BBB 01 CCC 04 How can I use SUMMARIZE to count by Date, Category and Salesperson? As not every salesperson has category on that day. I wish to have the result like this, no matter they have sales or not on each day. If no sales, display 0. Date Salesperson Category Count 2023-01-02 AAA BAT 1 2023-01-02 AAA TGA 0 2023-01-02 BBB BAT 0 2023-01-02 BBB TGA 0 2023-01-02 CCC BAT 0 2023-01-02 CCC TGA 0 2023-03-03 AAA BAT 0 2023-03-03 AAA TGA 1 2023-03-03 BBB BAT 0 2023-03-03 BBB TGA 2 2023-03-03 CCC BAT 0 2023-03-03 CCC TGA 0 2023-04-23 AAA BAT 0 2023-04-23 AAA TGA 0 2023-04-23 BBB BAT 0 2023-04-23 BBB TGA 0 2023-04-23 CCC BAT 1 2023-04-23 CCC TGA 1 With the summarize command I wrote it wouldn't include zero for everyone. How can I solve this issue? Link to pbix file Any help is greatly appreciated!
