Forum Discussion
Restricting Data in a Measure
Hi amitchandak
Many thanks for the assistance on this, but I think I have now worked it out myself.
To assist others that may view this thread, I needed to include an AND statement so the logic would consider another field from the standard isblank() that I was going to use.
Formula as follows for reference:
Item Days Remaining (Training Matrix Text Display) =if( and(max( 'Curriculum Status (FACT)'[Days Remaining (Item)])=0,max('Curriculum Status (FACT)'[Latest Completion]) <>BLANK()), "Last Completed:" & unichar(10) & format(max('Curriculum Status (FACT)'[Latest Completion]),"dd/mm/yyyy"),max('Curriculum Status (FACT)'[Days Remaining (Item)]))Where it references 'Curriculum Status (FACT)'[Days Remaining (Item)])=0, I adjusted the actual query to change the null values to 0 for those values that needed to be changed. Previously, this is the bit I was trying to handle using isblank().
Hi again...
Further to my previous comment, there will be nothing to differentiate the blank values because the same logic should be applied to the all users. Its almost like I need a way of restricting the data from being returned to only the slider filters that are applied. Any thoughts on how I can do this?
Anonymous , The filter you have not will add 0 to max value if it null. So do you need to handle null before or after
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi amitchandak
Many thanks for the assistance on this, but I think I have now worked it out myself.
To assist others that may view this thread, I needed to include an AND statement so the logic would consider another field from the standard isblank() that I was going to use.
Formula as follows for reference:
Item Days Remaining (Training Matrix Text Display) =if( and(max( 'Curriculum Status (FACT)'[Days Remaining (Item)])=0,max('Curriculum Status (FACT)'[Latest Completion]) <>BLANK()), "Last Completed:" & unichar(10) & format(max('Curriculum Status (FACT)'[Latest Completion]),"dd/mm/yyyy"),max('Curriculum Status (FACT)'[Days Remaining (Item)]))Where it references 'Curriculum Status (FACT)'[Days Remaining (Item)])=0, I adjusted the actual query to change the null values to 0 for those values that needed to be changed. Previously, this is the bit I was trying to handle using isblank().