Restricting Data in a Measure

[Anonymous]

Hi there,

I have the below measure which works, but I have a numbers of sliders which when filtered, returns many items that are not relevant.  My question is, how do I restrict the measure from returning the extra records that are being returned.  For instance, I have a user slider and when selecting a single user I would expect the matrix visualisation that I am using to only display the items related to that user but its returning all items that satisfy the formula below and does not account for the user that I have filtered with in the slider.  I think the solution is to restrict the measure to ensure it does not return the extra items of data, but the logic below is what I need - I just don't know how to restrict in this way.  Any help appreciated.  Its difficult to explain I guess.

Item Days Remaining (measure) =

if(

ISBLANK(

CALCULATE( MAX('Curriculum Status (FACT)'[Days Remaining (Item)]),

filter('Curriculum Status (FACT)','Curriculum Status (FACT)'[Item Competency Indicator]="Competent")

)

),

0,

1

)

[amitchandak]

@Anonymous , is blank or +0 in the formula will force left join. That will give the other value of item with 0 value

[Anonymous]

Hi @amitchandak 

Thanks so much for your quick reply.

You are exactly right and this is the issue for me..  it is returning all other items.  The problem is, I'm not sure how to restrict the items that I don't want displayed.  Do you have any advice for me to investigate and play with?

[amitchandak]

@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

[Anonymous]

@amitchandak 

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.

[Anonymous]

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?

[amitchandak]

@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.

[Anonymous · Accepted solution]

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().