Need "DAX formula measure" of Pivot data model to be added as Slicer OR in Filter

[Mehmud Khan]

Dear Experts,

Good day!

I need your support in small issue;


I have created a Data Model pivot where I have added DAX formula measure to evaluate some value based on "IF" login.

I need that measure to be added as Slicer or to be added in Filter field so as I can get final report in click.

Requesting all experts, to review the file and help me in this issue please.

Thanks in advance for your each and every support.




Regards,
Mehmud

 

[Debaser]

You can't use a measure for that, you need a calculated column. You could use something like:

=IF(SUMX(FILTER(Table1,Table1[User]=EARLIER(Table1[User])),Table1[Amount])>=15,"Exceeded","Not exceeded")

then add that new column as the slicer.

 

[Chihiro]

Another, bit more convoluted method.

Measure:= IF((Table1[Sum of Amount])>=15,1,0)

Add to field.

Apply custom number format to the measure.
[>0]"Exceeded";[=0]"Not Exceeded"

Then add value field filter.

 

[Mehmud Khan]

Another, bit more convoluted method.

Measure:= IF((Table1[Sum of Amount])>=15,1,0)

Add to field.

Apply custom number format to the measure.
[>0]"Exceeded";[=0]"Not Exceeded"

Then add value field filter.

View attachment 90557

Thanks you so much Chihiro....
your inputs are really helpful.

I am sorry but I need to do little changes, as below;
I have added the region field in the table which affecting my pivot table table.

I have followed your described steps which working in my previous pivot file.

Now, that Value filter is not working in this pivot format.

Any suggestion ??

Thanks in advance.

Regards,
Mehmud

 

[Mehmud Khan]

You can't use a measure for that, you need a calculated column. You could use something like:

=IF(SUMX(FILTER(Table1,Table1[User]=EARLIER(Table1[User])),Table1[Amount])>=15,"Exceeded","Not exceeded")

then add that new column as the slicer.

Thanks you so much Debaser..
you efforts are highly appreciated.

unfortunately, few formula are not working in my this version of excel.

any additional suggestion?

Thanks in advance.
Regards,
Mehmud

 

[Debaser]

Those are DAX to be used in a calculated column in the data model, not within the Excel data.

 

[Mehmud Khan]

Those are DAX to be used in a calculated column in the data model, not within the Excel data.

hello Debaser,

thanks for assistance.

I tried with the given formula in DAX but it doesn't work for me..

requesting you to check below error and assist me.


req

 

[Debaser]

It's a calculated column not a measure. You need to add it in the data model.