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().
Anonymous , Do not add +0 or handle the blank using if.
In case you want to display some intermediate value as 0, you may have to do some tweaking
How else could I handle the blank values in this case? The reason I need to handle the blank value is because my measure is using MAX and this will not include blank values. This means that when I use my measure its excluding all those values that are blank, when in fact I need to display them for those dimensions that are in my matrix visualisation. I think the answer is, I need to find or setup a column to identify the blanks that are relevant for the dimensions that I use in the visualisation but this is where I'm coming unstuck, because there is none! If there is no alternative to what I've just mentioned, then I will have to continue to review and come up with something that differentiates the blank values.
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().