Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have data in 2 tables
analytic_metrics
assets
These tables are related using a serial number.
The asset table houses a "last replaced date."
I'd like to run a report out of my analytic_metrics table that runs a sum of shots since the "last replaced date" in the asset table.
Am I able to use this last replaced date value as my filter?
Thanks
Solved! Go to Solution.
Hi @morganrosser ,
Please try below steps:
Create a measure with below dax formula:
Measure =
VAR cur_id =
SELECTEDVALUE ( Analytic_metrics[sim_id] )
VAR _date =
CALCULATE (
MAX ( Asset[Date_Replaced_hitting_Strip] ),
Asset[SerialNumber] = cur_id
)
VAR tmp =
FILTER ( ALL ( Analytic_metrics ), [sim_id] = cur_id && [date] > _date )
RETURN
IF ( ISBLANK ( _date ), 0, SUMX ( tmp, [shots] ) )
Add a table visual with Analytic_metrics[sim_id] and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @morganrosser ,
Please try below steps:
Create a measure with below dax formula:
Measure =
VAR cur_id =
SELECTEDVALUE ( Analytic_metrics[sim_id] )
VAR _date =
CALCULATE (
MAX ( Asset[Date_Replaced_hitting_Strip] ),
Asset[SerialNumber] = cur_id
)
VAR tmp =
FILTER ( ALL ( Analytic_metrics ), [sim_id] = cur_id && [date] > _date )
RETURN
IF ( ISBLANK ( _date ), 0, SUMX ( tmp, [shots] ) )
Add a table visual with Analytic_metrics[sim_id] and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @v-binbinyu-msft ,
Thanks for the reply.
Apologies for the delay, I had some travel mixed in that made it tough to respond.
I was able to use your dax formula to create the measure. But I uncovered an issue in field formatting in my tables. The sim_id is an integer while the sim ID is text/number.
Should I create a measure that converts the sim_id to a number or do I build that into the provided dax formula?
Thanks
yes, that is possible.
Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi, thanks for the response!
Sample Data
Table 1: analytic_metrics
sim_id | date | shots | location_id |
20693482 | 10/20/2022 | 100 | 2 |
20693482 | 10/18/2022 | 250 | 2 |
70321005 | 10/20/2022 | 75 | 79 |
20693482 | 10/10/2022 | 50 | 2 |
Table 2: Asset
SerialNumber | Date_Replaced_hitting_Strip |
20693482 | 10/17/2022 |
I have the above two tables where
analytic.sim_id = Asset.SerialNumber
I want to create a table where I can show shots on sim_id/SerialNumber = 20693482 since the Date_Replaced_Hitting_Strip (10/17/2022). A table format would be perfect:
Filters
Sim_id = 20693482 (I'm fine hard coding this filter as we need)
analytic_metrics.Date = Asset.Date_Replaced_Hitting_Strip
sim_id | sum of shots |
20693482 | 350 |
Thanks!
User | Count |
---|---|
90 | |
85 | |
66 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |