Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I have a schema in which multiple tables are connected and I have a table of measures on these tables. The key table in this schema is a table of Work Orders (WOs) which has a list of dates against which work orders are listed.
I have now generated a table of start and end dates. I want to re-calculate a measure for these dates as max and min by filtering the WOs table.
I tried this using the below formula but I did not get any result (Just blank):
Solved! Go to Solution.
I found the perfect solution for this issue. Here are 2 measures I wrote to arrive at the answer:
measure 1:
Are you looking for a query something like this?
CALCULATE(
CALCULATE([Overall System Availability_product],DATESBETWEEN(WOs[REPORTDATE+Time], MAX('Table'[Start]),MAX('Table'[End])),ALLSELECTED('Table'))
,ALL('Test Freq + Config'[SECE Group]), ALL(Measures_Table))
I think this will give a re-calculation for min and max for the 2 columns. I was hoping to get a value for each row. I hope this makes sense. Thanks. Apologies
@vslaser , Not very clear .
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
@Amit, can you please send me a contact method? I am unable to upload the pbix file here. What I want to do is to generate a trend with the value re-calculated for each date range in the rows of this table. At the moment, it is not taking into consideration the start and end dates in the table which is why the value remains unchanged in the rows
Hi @amitchandak ,
Please find the file at the below link:
https://drive.google.com/file/d/17ma0SUX4Kx9X9AGwSyXq2VLX39C_H7Ev/view?usp=sharing
Any help would be deeply appreciated.
Thanks,
Vijay
Hi,
Please share the exact result that you are expecting.
@vslaser Use this formula and I guess it works as per your requirement
Calculated = calculate([Overall System Availability_product],DATESBETWEEN(Measures_Table[Date], MAX('Table'[Start]),MAX('Table'[End])),ALLSELECTED(WOs),ALL('Test Freq + Config'[SECE Group]))
Not sure why you have used "ALLSELECTED(WOs)" but I kept it as it is. And as mentioned earlier DATESBETWEEN will help you to filter rows by given dates
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Cheers!
@vslaser wrote:Hi @amitchandak ,
Please find the file at the below link:
https://drive.google.com/file/d/17ma0SUX4Kx9X9AGwSyXq2VLX39C_H7Ev/view?usp=sharing
Any help would be deeply appreciated.
Thanks,
Vijay
@amitjzaveri , the solution you provided is only working for 1 of the SECEs and not for others and the values dont seem to be for all the ones in table columns 😞
Hi all,
I used the below formula and am getting a value for each of the rows in the 'Table' table. These values however are still wrong.
I found the perfect solution for this issue. Here are 2 measures I wrote to arrive at the answer:
measure 1:
Thanks @amitchandak ,
I tried the solution you gave me but it looks like it now does not change with the selection of SECE group in the page. The same result is displayed for all SECE groups 😞
@Ashish_Mathur : The result I expected was:
recalculated values of the verall availability for the given dates for the selected SECE group. Unfortunately, I am unable to calculate them here because even with the help @amitchandak provided, the result is not specific to the SECE group. 😞
Regards,
Vijay
Thanks Amit. I will share a sample
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |