topic Re: DAX function returns blank in card in DAX Commands and Tips

DAX function returns blank in card

vissvess — Fri, 09 Aug 2019 06:35:10 GMT

Hi,

Could anyone please clarify why the following measure returns "Blank".

Running chassis matching with F =
VAR overallmax = [Overall max pallet positions per 3hr bucket]
VAR maxpalletoccuredday =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        FILTER (
            VALUES ( 'Calendar'[Date] ),
            [Max Pallet positions per day] = overallmax
        )
    )
VAR maxpalletoccuredbucket =
    CALCULATE (
        MAX ( 'Time Buckets'[Bucket] ),
        FILTER (
            VALUES ( 'Time Buckets'[Bucket] ),
            [New Pallet requirement] = overallmax
        )
    )
RETURN
    maxpalletoccuredbucket

I doubt that, the max worked for date. But the second time, the max is applied on data type text.

I am not sure that max fits in here. Could anyone lists the dax function that results a single value similar to max but not arithmatic function.

Thanks

Re: DAX function returns blank in card

vissvess — Fri, 09 Aug 2019 06:39:11 GMT

In short, I am in need of finding the value that corresponds to max value of a measure.

In detail,

I have a measure which returns a particular value.
I want to get the particular field value which corresponds to the max of measure output.

I am not well in dax. So, feel free to neglect my code in case and propose new code.

Any help would be so thankful.

Thanks in advance.

Re: DAX function returns blank in card

Mariusz — Fri, 09 Aug 2019 07:33:53 GMT

Hi vissvess

Can you create a data sample that represents your requierment and use this sample to show the desired outcome?

Best Regards,
Mariusz

Please feel free to connect with me.

Re: DAX function returns blank in card

Anonymous — Fri, 09 Aug 2019 18:16:17 GMT

Running chassis matching with F =
VAR __overallMax = [Overall max pallet positions per 3hr bucket]
VAR __maxPalletOccuredBucket =
    FILTER (
        VALUES ( 'Time Buckets'[Bucket] ),
        [New Pallet requirement] = __overallMax
    )
RETURN
    __maxPalletOccuredBucket

You can try the above but since I can't see the model, don't know the measures used and don't really know what the code does in the context you try to use it in, I can't be of more help. FILTER should return one value. If it returns more, then you'll get an error.

Best

Darek

Re: DAX function returns blank in card

vissvess — Sat, 10 Aug 2019 01:53:21 GMT

Dear Anonymous ,

The code worked great. As mentioned by you. There is a one time occurance of the max value. So the code doesnot returned an error.

But could you please clarify, what to do if I get an error in future as the dataset may get updated, so that there are two field values with the max. What to do to get the any one or latest or both in the list that to be used for slicer.

Please extend your kind help further.

Thanks

Re: DAX function returns blank in card

Anonymous — Sat, 10 Aug 2019 14:02:53 GMT

If you get 2+ time buckets, then you have to decide what rule you want to apply to get one of them as the value of your measure. It's your choice. I can't help you with this. All I can do is try to figure out how to implement rules and algorithms. I can't create them for you.

Best
Darek

Re: DAX function returns blank in card

vissvess — Wed, 14 Aug 2019 07:17:12 GMT

Hi Anonymous ,

You are correct. The logic should be mine. I need the max value with latest date.

Kindly suggest to improve the code.

Thanks

VissVess

Re: DAX function returns blank in card

Anonymous — Wed, 14 Aug 2019 09:37:22 GMT

Maybe this could work? I'm not sure since I don't know all the details of the model.

Running chassis matching with F =
VAR __overallMax = [Overall max pallet positions per 3hr bucket]
VAR __maxDate =
    MAXX (
        FILTER (
            VALUES ( 'Calendar'[Date] ),
            [Max Pallet positions per day] = overallmax
        ),
        'Calendar'[Date]
    )
VAR __maxPalletOccuredBucket =
    CALCULATE(
        VALUES ( 'Time Buckets'[Bucket] ),
        FILTER (
        	VALUES ( 'Time Buckets'[Bucket] ),
        	[New Pallet requirement] = __overallMax
        ),
        'Calendar'[Date] = __maxDate
    )
RETURN
    __maxPalletOccuredBucket

Best

Darek