topic Re: How to show max value from slicer in pivot in DAX Commands and Tips

How to show max value from slicer in pivot

GeorgWildmoser — Mon, 05 Aug 2024 12:20:49 GMT

Hi,

I have a pivot with documents and a slicer on the document date. The pivot contains the document and the respective document date.

My problem: The pivot should be extended by another column - with the maximum selected document date of the slicer. The result is a cross product.

What am I doing wrong? Thank you for your help.

MyMeasure: MaxSliceDocumentDate = maxx(allselected(TimeDoc), TimeDoc[Date Date])

Starting Point:

Result after adding Measure:

Needed Result:

Re: How to show max value from slicer in pivot

Gabry — Mon, 05 Aug 2024 13:44:11 GMT

Hello GeorgWildmoser

i think you just need to add a check

MaxSliceDocumentDate = if(isblank(YourSumMeasure), blank(),maxx(allselected(TimeDoc), TimeDoc[Date Date]))

we could also think on why you are using maxx instead of just max

Let me know if it works

bye

Re: How to show max value from slicer in pivot

GeorgWildmoser — Tue, 06 Aug 2024 07:48:58 GMT

Hi Gabry,

thank you very much for your support. Your solution works fine.

Your suggested Measure (works great):

MaxSliceDocumentDate =

if(isblank([Betrag]),blank(),maxx(ALLSELECTED(TimeDoc),TimeDoc[Date Date]))

Many thanks and have a nice day.

Georg

Re: How to show max value from slicer in pivot

Anonymous — Tue, 06 Aug 2024 09:01:13 GMT

Thanks for the reply from Gabry , please allow me to provide another insight:
Hi GeorgWildmoser ,

As far as I know, Power BI automatically omits blank lines and treats blank fingers as zeros after a Sum operation.

Note: Blank lines are automatically omitted if "Show items with no data" is unchecked, but if this option is selected, blank values will also be shown.

In your data there are [DimVo] columns, each row has a separate index, after using [MaxSliceDocumentDate] the expression will be applied to each row, so it will display the value on each row.

If you only want to show columns where [Sum] has a value, you can use the superuser's measure above to determine if [Sum] has a value, and if it does, then [MaxSliceDocumentDate] is displayed, and if it doesn't, then it's blank.

You can also use Filters, select [Sum] -- is not blank

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.