Forum Discussion
Compare / Combine Data
I revised the two measures below:
Audit Date 1 = IF ( MAX ( Table2[AuditScorePercentage1] ) <> BLANK (), MAX ( Table2[Date1] ) )Audit Date 2 = IF ( MAX ( Table2[AuditScorePercentage2] ) <> BLANK (), CALCULATE ( MAX ( Table2[Date1] ), Table2[AuditScorePercentage2] <> BLANK () ) )
I revised the two measures below:
Audit Date 1 =
IF ( MAX ( Table2[AuditScorePercentage1] ) <> BLANK (), MAX ( Table2[Date1] ) )Audit Date 2 =
IF (
MAX ( Table2[AuditScorePercentage2] ) <> BLANK (),
CALCULATE ( MAX ( Table2[Date1] ), Table2[AuditScorePercentage2] <> BLANK () )
)
DataInsights Thank you very much! That all worked very well. I have one last question, what is the best way to handle changes in the "Date"?
For example, if I only want it to show audits with dates from Quarter 1 or if I want it to show inspecitons from the last 180 days. I added filters to each date which works, but some data is still missing when using filters. Thank you!
Glad to hear that worked. I recommend creating a date table and using fields from the date table in a slicer or filter. Create a relationship between the date table and Table2. This will result in a star schema.
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
DataInsights Thank you! Do you have a sample date table?
I believe I got this to work, but I have not finished testing, see my table here and feel free to judge how it looks or if there are more fields that should be added to further filter dates.
Thank you!
See the link below. You may not need all these columns, so I would focus on what you want from your data model, and create those columns.
https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/
Here's a link to a date table in Power Query:
https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query