How to apply slicer on the calculated column value

[robleek100]

Hi,

I have running data in table, I have created few measure and calculated column. In Calculated column I have compared value is greater than X column if yes then "Limit Exceeded" Else "Under Limit".

I have pulled those measure into that Matrix visualizaton now I want to apply Slicer based on the Calculated column which has Limit Exceeded and Under Limit values.

When I apply slicer Its pulled out entire table data instead of the visualiztion data which has filter result. I want my slicer to be used on the filter data rather than pulling entire data.

-------

Filtered Data Below (6 rows returned)

Slicer (When I choose Limit Exceeded from the slicer then it should show only red highlighted data)

When I choose Under Limit it should show only two results i.e. B & C.

Table – Data in a Table

Slicer on filtered column

Thanks,

Rob

[v-piga-msft]

Hi @robleek100,

By my test with creating a calculated column similar to yours, but I cannot reproduce your issue.

If it is convenient, could you share a dummy pbix file which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

You also could refer to my attachment.

Best  Regards,

Cherry

[robleek100]

Please find below .pbix file link

https://drive.google.com/open?id=1yimTF1b7elOho2Fk7yYx_kDywA0B52

[v-piga-msft · Accepted solution]

Hi @robleek100,

Based on your sample, I found that your issue may caused by the measure Credit Status and the calculated column Credit Limit Status.

The measure always calcuated the Aggregate value but the calculated column not. So when you select the calcuated column, it will not filter the aggregate value only the row context.

Please create a new calculated table to get the aggregate value, then calculated columns and the measure with the formulas below.

Table =
SUMMARIZE (
    'Sheet1',
    'Sheet1'[Cust Name],
    "max of C", MAX ( 'Sheet1'[Cust. Exp] ),
    "sum ub", CALCULATE ( SUM ( 'Sheet1'[UB] ) ),
    "sum qu", CALCULATE ( SUM ( Sheet1[Quote Net Value] ) ),
    "max of credit limit", CALCULATE ( MAX ( 'Sheet1'[Credit Limit] ) )
)
option = IF([UB+QU]>[max of credit limit],"Limit Exceeded","Under Limit")

UB+QU = 'Table'[sum qu]+[sum ub]

Measure 2 = 
VAR a =
    CALCULATE ( MAX ( 'Table'[UB+QU] ) )
VAR b =
    CALCULATE ( MAX ( 'Table'[max of credit limit] ) )
RETURN
    IF ( b < a, "Red", "White" )

Here is the output.

 

More details, please refer to the attachement.

Best  Regards,

Cherry