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 () ) )
DataInsights Thank you for the assistance here! I made one mistake and this is where I am stuck - the dates are where I have the most issues:
Any ideas here?
Table 2
BuildingNameColumn Date 1 AuditScorePercentage 1 AuditScorePercentage 2
Building 1 3/23/2022 95.00% -
Building 1 3/21/2022 - 95.00%
Building 4 3/15/2022 95.00% -
Building 4 3/14/2022 - 95.00%
Building 6 3/08/2022 95.00% -
Desired Result
BuildingNameColumn AuditScorePercentage 1 Date1 AuditScorePercentage 2 Date2
Building 1 95.00% 3/23/2022 95.00% 3/21/2022
Building 2 - - - -
Building 3 - - - -
Building 4 95.00% 3/15/2022 95.00% 3/14/2022
Building 5 - - - -
Building 6 95.00% 3/08/2022 - -
Building 7 - - - -
Building 8 - - - -
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!