Distinct count total is correct, but the columns are not
Hi, I am new to power BI and need some help. I have a list of patient IDs and I want to get the total distinct patients per fiscal year broken down by fiscal period, but when I put it in a table with columns representing the periods, it gives me the distinct total per period not per year, which is not what I want. Only the grand total in the table is correct. e.g. Here is what I'm getting for 2023 (using a slicer): Here's what I want: Again, to clarify, each patient should only be counted once per year, and should only be counted in the first period they appear. In my dates table, I have per row every date from 2022-2025 (Jour"), and the corresponding fiscal period (P#) and fiscal year (Annee 2). In my patient data table, the relevent columns would be the patient ID and appointment date (startdatetime). I have done a lot of research and none of what I've read is giving me the correct results. I can acheive the results when working with imported data, but I need to keep it as direct query so my options are more limited. I would appreciate any help you can give!
Distinct Count of Total Averages
Hello everyone, I have a spreadsheet with temperature records from several loggers. I need to find out how many loggers have an average temperature above a certain value (temperature rounded down without decimals). I can calculate this using a combination of a pivot table and Excel formulas, but I can't create a calculated field or measure in the pivot table (PowerPivot) that would do this automatically and of course dynamically based on the filters and slicers used, as you can see in the attached file. Distinct Temp.xlsx
Help Writing DAX Expression Need to count number of values in a table, but ignore duplicate rows
Hello all, I utilize power BI for reporting in my role, and I need help writing a DAX expression. I work in a call center. And we are able to export data from our ticketing system. This ticketing system generates a unique incident number for each ticket created. We also have a knowledge base that we use in our department with various knowledge articles (KBA Title), if a team member follows a spcifiec knowledge article (KBA Title) when working/ or troubleshooting an issue, they are supposed to pin the knowledge article to the incident number in our ticketing system. I need to calculate the percentage of how many knowledge articles are pinned compaired to incident numbers created. Step 1 of this is to figure out how many knowledge articles are pinned. When reviewing the data I've found that sometime the rows are duplicating/ showing multiple rows with the same incident and KBA Title. If multiple KBA Titles are used on the same Incident Number, that's fine. but I need to ignore duplicate rows with the same Incident Number and same KBA Title when counting. Below is an example data source. I added the KBA Count column manually to illustrate what I am trying to acomplish. In red text is an example of the duplicated rows I need to ignore in counting. I count the first occurance, but do not count the second. Pinner KBA TItle Incident Number Last Modified Date INC Submitter KBA Pinned by TK Creator INC Submit Date KBA Count 3040887 KBA00003155 INC000000943137 10/29/2023 9:52 3040887 1 10/29/2023 0:09 1 3040887 KBA00006728 INC000000943140 10/29/2023 10:20 3040887 1 10/29/2023 0:10 1 3040887 KBA00000969 INC000000943151 10/29/2023 11:25 3040887 1 10/29/2023 0:11 1 3040887 KBA00001225 INC000000943193 10/29/2023 13:04 3040887 1 10/29/2023 0:13 1 3040887 KBA00002905 INC000000943193 10/31/2023 14:48 3040887 1 10/29/2023 0:13 1 3040887 KBA00006728 INC000000943304 10/29/2023 10:39 3040887 1 10/29/2023 0:10 1 3040887 KBA00002760 INC000000943330 10/29/2023 12:30 3040887 1 10/29/2023 0:12 1 3040887 KBA00001330 INC000000943337 10/29/2023 12:43 3040887 1 10/29/2023 0:12 1 3040887 KBA00003689 INC000000946386 10/31/2023 9:45 3040887 1 10/31/2023 0:09 1 3040887 KBA00001225 INC000000946394 10/31/2023 9:55 3040887 1 10/31/2023 0:10 1 3040887 KBA00002905 INC000000946394 10/31/2023 14:48 3040887 1 10/31/2023 0:10 1 3040887 KBA00001511 INC000000947617 11/1/2023 4:33 3040887 1 11/1/2023 0:04 1 3040887 KBA00002629 INC000000947621 11/1/2023 5:08 3040887 1 11/1/2023 0:05 1 3040887 KBA00002555 INC000000947621 11/1/2023 5:08 3040887 1 11/1/2023 0:05 1 3040887 KBA00001448 INC000000947624 11/1/2023 5:42 3040887 1 11/1/2023 0:05 1 3040887 KBA00003389 INC000000947628 11/1/2023 6:16 3040887 1 11/1/2023 0:06 1 3040887 KBA00001123 INC000000947643 11/1/2023 7:51 3040887 1 11/1/2023 0:07 1 3040887 KBA00006215 INC000000947647 11/1/2023 7:56 3040887 1 11/1/2023 0:07 1 3040887 KBA00000969 INC000000947682 11/1/2023 9:42 3040887 1 11/1/2023 0:09 1 3040887 KBA00000969 INC000000947682 11/1/2023 9:42 3040887 1 11/1/2023 0:09 0 3040887 KBA00005305 INC000000947700 11/1/2023 10:14 3040887 1 11/1/2023 0:10 1 3040887 KBA00004268 INC000000948096 11/1/2023 10:45 3040887 1 11/1/2023 0:10 1 3040887 KBA00003389 INC000000948509 11/1/2023 11:14 3040887 1 11/1/2023 0:11 1 3040887 KBA00001364 INC000000948543 11/1/2023 12:13 3040887 1 11/1/2023 0:12 1 3040887 KBA00001364 INC000000948543 11/1/2023 12:13 3040887 1 11/1/2023 0:12 0 3040887 KBA00001381 INC000000949031 11/2/2023 4:23 3040887 1 11/2/2023 0:04 1 3040887 KBA00005305 INC000000949034 11/2/2023 4:58 3040887 1 11/2/2023 0:05 1 3040887 KBA00003997 INC000000949721 11/2/2023 9:25 3040887 1 11/2/2023 0:09 1 3040887 KBA00001225 INC000000949728 11/2/2023 9:36 3040887 1 11/2/2023 0:09 1 3040887 KBA00002889 INC000000949728 11/2/2023 9:38 3040887 1 11/2/2023 0:09 1 3040887 KBA00003145 INC000000949743 11/2/2023 10:06 3040887 1 11/2/2023 0:10 1 3040887 KBA00003145 INC000000949902 11/2/2023 11:13 3040887 1 11/2/2023 0:11 1 The total for this data should be 30 KBAs. However, I cannot get the DAX to display that information. I am using Distinct count and Count to try and calculate the data. I don't know of a better way to model the data so I can automatically generate the KBA count column. Got this from a separate PBI Forum post that sounded close. It is returning a count of all Incident Numbers KBA Use Count 2 = CALCULATE(DISTINCTCOUNT('KBA Linked to Incidents CSV'[Incident Number]), 'KBA Linked to Incidents CSV'[KBA Pinned by TK Creator]=1, FILTER(SUMMARIZE(VALUES('KBA Linked to Incidents CSV'[KBA ID]),"ABCD", COUNTROWS('KBA Linked to Incidents CSV')), [ABCD]>1)) This is me throwing things together after trying to talk it out. KBA Use Count 5 = CALCULATE(DISTINCTCOUNT('KBAs Linked to Incidents CSV'[KBA TItle]), DISTINCT('KBAs Linked to Incidents CSV'[Incident Number]), 'KBAs Linked to Incidents CSV'[Pinned by Submitter]=1) I am at a loss so reaching out for assistance. Thanks for everyone's time!Counting orders based on their latest status
Hello, For some time now I am strugling with measure that will count orders based on their last status. I tackled this from multiple angles and even ask ChatGPT for help, but couldn't find a solution. My goal is table visualization with columns for: names of statuses in first column (STATUS_DICTIONARY[NAME]), number of orders which have given status as their latest, % of GT My model looks like this: ORDERS[ID] - 1 to * - ORDERS_STATUS[ID_ORDER] STATUS_DICTIONARY[ID] - 1 to * - ORDERS_STATUS[ID_STATUS] And below is some sample data: ORDERS ID NUMBER 1 23/01/1 2 23/01/2 3 23/01/3 4 23/01/4 5 23/01/5 ORDERS_STATUS ID ID_ORDER ID_STATUS DATE_CHANGE 1 1 1 26.09.2023 2 1 2 26.09.2023 3 2 1 26.09.2023 4 3 1 27.09.2023 5 4 1 27.09.2023 6 1 3 27.09.2023 7 2 5 28.09.2023 8 3 2 28.09.2023 9 3 3 29.09.2023 10 5 1 30.09.2023 11 4 2 30.09.2023 STATUS_DICTIONARY ID NAME 1 aaa 2 bbb 3 ccc 4 ddd 5 eee Based on all above I expect result to look like this: STATUS NAME NUMBER OF ORDERS % OF GT aaa 1 20% bbb 1 20% ccc 2 40% ddd 0 0% eee 1 20% I will be very gratefull for any help. Thank you!DAX Optimisation Cumulative DISTINCTCOUNT
Hi All, I am wondering if someone can help me with a very slow DAX calculation. Business Case: We consider a customer to be financially active on our system for a financial year if the sum of their transactions for a financial year for any Group ID is greater than 0. Our financial year lasts from 1st of August and ends 31st of July. Product ID's are grouped under a Grouping ID. DAX I created a grouping calculated column in the customer table, (Fiscal Year || Grouping ID || Customer ID) { to avoid having to create joins in the query step). I want to calculate the number of financially active customers ( Active Customer Count ) and a cumulative count of this metric (Cumulative Active Customer Count). However the cumulative sum is very slow (20-30 seconds long) Active Customer Count = CALCULATE( DISTINCTCOUNT('Customer Tbl'[Customer ID]), FILTER( ALL('Customer Tbl'[Fiscal Year || Grouping ID || Customer ID]), [Transaction Amnt]>0)) Cumulative Active Customer Count = CALCULATE( [Active Customer Count], CALCULATETABLE( DATESYTD('Dim Date'[Date], "31-07"), 'Dim Date'[Is Future Date] = "Not Future Date" ) ) Any idea on what I can do to speed up this cumulative count? Link to file: https://drive.google.com/file/d/18OuoPSx2pFmk0Q-NCU1hhPcFyt_pQvr3/view?usp=sharingCount Indicator Achievement [DistinctCount, Summarize, Countrows]
Hi there, Newbie is here. I have been stuck for 1 month to fix my dashboard. Hope someone can help me. I have a raw data like this: IndicatorName Month Target Actual Indicator1 01/01/2021 0 5 Indicator2 01/01/2021 5 1 Indicator3 01/01/2021 30 20 Indicator4 01/01/2021 2 0 Indicator5 01/01/2021 0 0 Indicator1 01/02/2021 5 0 Indicator2 01/02/2021 2 1 Indicator3 01/02/2021 0 8 Indicator4 01/02/2021 0 0 Indicator5 01/02/2021 0 0 Indicator1 01/03/2021 2 7 Indicator2 01/03/2021 0 2 Indicator3 01/03/2021 10 17 Indicator4 01/03/2021 8 4 Indicator5 01/03/2021 0 0 And I've managed to get this kind of table which is as expected - with slicer month active Indicator Target Actual Achievement Indicator1 [Sum(Target)] [Sum(Actual)] divide (Sum(Actual), Sum(Target)) Indicator2 " " " Indicator3 " " " Indicator4 " " " Indicator5 " " " But when I want to use a Card that visualizes the "number of indicators that reach the target by at least 90%", I don't get what I expect. I thougt it was because I should use the summarize function. But when I use summarize function, month slicer didn't work. What I am expecting is, when I Click the Month slicer for 'month 01' and 'month 02', the Card should showing: I have managed for VAR Indicator with Target: Indicator with Target = CALCULATE(DISTINCTCOUNT(Query1[Indicator]),FILTER(Query1,Query1[Target]>0)) But for the Indicator Achieved Target, I still stuck to produce this variable. SUM Achievement Indicator = Query1[SUM Actual]/Query1[SumTarget] Indicator Achieved Target = CALCULATE(DISTINCTCOUNT(Query1[Indicator]),FILTER(query1,Query1[SUM Achievement Indicator]>0.89)) --> the result is not as expected. Really appreciate your support on this. Thank you in advance.
