Need help to Get latest records from table data by adding flag
Need to get latest record based on each file group on below conditions. PBIX URL : https://drive.google.com/file/d/1YNkpQQKhtOejgPtj48bmS6v6MyadYnfN/view?usp=sharing 1.Sourcesystem="DB" is latest. 2. if file is not available in Sourcesystem="DB" ,check Sourcesystem="App" and Sourcesystem="app" and stepdescription = "Import " is latest. 3. IF stepdescription = "Import " also file not available. stepdescription = "Transfer " is latest. Pls find below screen shot for source data and highlighted color indicates expected output. Tried below script but expected result not coming . need help. Test 1 = IF ( CALCULATE( COUNTROWS( VALUES('Data'[FileName]) ), FILTER( ALL('Data'), 'Data'[FileName] = EARLIER('Data'[FileName]) && 'Data'[SourceSystem] = "DB" ) ) > 0, IF ( 'Data'[SourceSystem] = "DB" , 1, 0 ), IF ( 'Data'[SourceSystem] = "App" && ( CALCULATE( MAX('Data'[StepDescription]), FILTER( ALL('Data'), 'Data'[FileName] = EARLIER('Data'[FileName]) && 'Data'[SourceSystem] = "App" && 'Data'[StepDescription] = "import" ) ) = 'Data'[StepDescription] || CALCULATE( MAX('Data'[StepDescription]), FILTER( ALL('Data'), 'Data'[FileName] = EARLIER('Data'[FileName]) && 'Data'[SourceSystem] = "App" && 'Data'[StepDescription] = "transfer" ) ) = 'Data'[StepDescription] ), 1, 0 ) ) above script giving output like below .
How to export the description info from the Model View in Power BI?
Hi Team, In the Model View, there is a "Description" info show on the Properties section. May I know how to export the "Description" info for all columns? or is there any effience way to show the rename column vs. origianl column? Thanks! Best regards, Mandy
total sum value in table visual is wrong
im trying to calculate the total commission by all salespersons. i have 2 tables , one table shows the commission by catgory and the other table shows sales transactions by salespersons. i created a joining column in power query by concatenating 3 columns in the packages table to have 1 unique key in the tabel so that i can join with the other table. after i joined the two tables with 1 to many relationship and cross filteting then created a table visual that shows : agent name - categoty-sales-commission value the sales total is correct but the commission is not correct. the commission is taken from the categories commission table and the rest of columns are from the categories transactions table. the correct total commission should be much higher=973.605 any idea of what could lead to wrong total sum value while value per row is correct
Help with DAX
Hi All, Hope you are all safe and well. Just want some help with a DAX please as follows. I'm creating a dashboard with lead start date from Leads entity and want total distinct customers from Customers table to be displayed who have start date >= Opportunity close date. Also, want to get purchase amount total for the customers to be available as well. So, based on the lead start date filter, the respective opportunities who are having link to Account which is connected to customers through service table needs to be displayed. The relationship from power bi model is as follows. Hope to hear from you. Regards Sanj
Measure for average processing time with data slicers
Hello everybody! I have the following scenario: I have a table with order IDs. This table also contains the creation date of the orders and the current check date (every seven days). There are also two columns for orders in process and finished orders. That means on every check date (every seven days) a new row is created for each order, where the current status of the order (In process or finished) is newly evaluated and marked in the corresponding column. This is the table with example data: OrderId CreatedAt CheckDate InProgress Finished 1 11.01.2021 25.01.2021 X 2 18.01.2021 25.01.2021 3 18.01.2021 25.01.2021 4 25.01.2021 25.01.2021 1 11.01.2021 01.02.2021 X 2 18.01.2021 01.02.2021 X 3 18.01.2021 01.02.2021 4 25.01.2021 01.02.2021 X 5 01.02.2021 01.02.2021 X 6 01.02.2021 01.02.2021 1 11.01.2021 08.02.2021 X 2 18.01.2021 08.02.2021 X 3 18.01.2021 08.02.2021 4 25.01.2021 08.02.2021 X 5 01.02.2021 08.02.2021 X 6 01.02.2021 08.02.2021 X 7 08.02.2021 08.02.2021 Now I want to create a measure that always calculates the average processing time for a selected check date. This is my current calculation for it: Average Processing Time (in days) = var _processingTime = CALCULATE( ((COUNTROWS(Data)*FIRSTDATE(Data[CheckDate])) - SUM(Data[CreatedAt]))/COUNTROWS(Data), FILTER(KEEPFILTERS(Data), Data[CheckDate]=SELECTEDVALUE(Data[CheckDate])), FILTER(KEEPFILTERS(Data), Data[InProgress]<>BLANK() ||Data[Finished]<>BLANK()) ) return _processingTime The problem is that my Measure does not use the check date when an order was first given the status "In Progress" or "Finished". Instead, it always uses the check date selected by the filter. For example, in the screenshot the measure shows a result of 15.40 days. But the correct solution should be 8.4 days as is shown here, which I need for my report: Correct Average Processing Time for CheckDate=08.02.2021: Id CreatedAt Processed first at CheckDate Days 1 11.01.2021 25.01.2021 14 2 18.01.2021 01.02.2021 14 4 25.01.2021 01.02.2021 7 5 01.02.2021 01.02.2021 0 6 01.02.2021 08.02.2021 7 Calculation (14 + 14 + 7 + 0 + 7) / 5 = 8,4 And this is the current wrong calculation of my measure: Current wrong calculation of Average Processing Time for CheckDate=08.02.2021: Id CreatedAt last CheckDate Days 1 11.01.2021 08.02.2021 28 2 18.01.2021 08.02.2021 21 4 25.01.2021 08.02.2021 14 5 01.02.2021 08.02.2021 7 6 01.02.2021 08.02.2021 7 Calculation (28 + 21 + 14 + 7 + 7) / 5 = 15,4 Does anybody have an idea how to rewrite this measure to get the correct average processing time, even when I filter my data with the data slicers as shown in the screenshot? Hope you guys can help me out! Thank you and kind regards! JMPowerBIAverage of a count entries without group by
Hello. I have a some columns in my file that correspond to customer's orders: order date, payment date, Number of order, product typology, etc. The behavior of the file is: when some customer order more than 1 item, it creates two lines for the same order, for example: Num order Orderdate Sku article typology Count of Num articles Mono or multi orders 123456 10/12/2019 65433 532 2 Multi 123456 10/12/2019 65990 597 2 Multi 23456 10/12/2019 65990 597 1 Mono The 2 last columns were calculations done by me: if a order number appears more than 1 time, it is an order that have more than 1 article. Now I want to know the average of article's per multi orders but I don't want to do a group by (by Number of order) to perform this. The way I know is to perform a query that groups by Num of order (all lines of the same order are consolidated in only one) and then do the average. How to do? I tried "average_num_articles:=CALCULATE(AVERAGE(Table[Count of Num articles]);FILTER(Table;VALUES(Table[NumOrder])))" without success. Thank you.
