Nested Filter Dax query to achieve sales for maximum date

[mwadhwani]

Hello Experts,

I am facing issue while creating measure for sum of Sales for max dateid with mapid=5.

Below is the sample data:

Table Name: Dummy

I tried using :   Calculate(sum(Sales),Filter(Filter(Dummy,Dummy[mapid]=5),Dummy[dateid]=max(Dummy[dateid])).

Any help would be appreciated.

Thanks

[vcastello]

Hi @mwadhwani

You may try ...

CALCULATE(
    Sum(Sales),
    FILTER(
         Dummy,
         Dummy[mapid]=5 &&
         Dummy[dateid]=max(Dummy[dateid])
     )
)

Hope That Helps,

Vicente

[mwadhwani]

Thanks for the reply

I tried: 

CALCULATE(
    Sum(Sales),
    FILTER(
         Dummy,
         Dummy[mapid]=5 &&
         Dummy[dateid]=max(Dummy[dateid])
     )
)

But I am getting Blank value.

[vcastello]

Hi @mwadhwani

Sorry, I made a mistake. You should try ... 

CALCULATE(
    Sum(Dummy[Sales]),
    FILTER(
         Dummy,
         Dummy[mapid]=5 &&
         Dummy[dateid]=max(Dummy[dateid])
     )
)

I've tried it on PowerPivot and it works for me ...

Hope That Helps

Vicente

[mwadhwani]

In PowerPivot it works fine. But when we create measure using the DAX it gives blank value.

[vcastello]

Hi @mwadhwani

Sorry, but I don't understand

What do you mean by ... 'using the DAX' ?

Vicente

[mwadhwani]

I am trying to create New Measure using your query:

CALCULATE(
    Sum(Dummy[Sales]),
    FILTER(
         Dummy,
         Dummy[mapid]=5 &&
         Dummy[dateid]=max(Dummy[dateid])
     )
)

But when I plot on say  Card visualization it gives blank value.

[vcastello]

Hi @mwadhwani

It works for me ...

Hence that, in Spain, we use ";" instead of "," in the formula editor

[mwadhwani]

Using suggested query by @vcastello,I am still getting Blank value again. Check this below pbix:

https://www.dropbox.com/s/qbielpmhexsop2j/CheckThis.pbix?dl=0

My Result screenshot:

IF I hardcode the value(634 which is maximum for mapid 12) instead of using MAX(ProcessDateID) in Filter, I get right output.
But using formula it shows Blank

Your help would be highly appreciated.

Thanks

[vcastello]

Hi @mwadhwani

The reason why the formula returns blank() is :

1.- Filters the table f_WS.
2.- Looking for records which f_WS[MapID] = 12 
3.- AND

4.- Looking for records which f_WS[ProcessDateID] equals the maximum value of f_WS[ProcessDateID] which is 640.

As there are no records that match that criteria (f_WS[MapID] = 12 AND f_WS[ProcessDateID] = 640) it returns blank()

What are you trying to achieve?

The SUM of f_WS[Value] for the MAXIMUM value of the f_WS[ProcessDateID]  for each f_WS[MapID] ?

Vicente

[mwadhwani]

First I want to filter f_WS[MapID]=12.
Subset obtained by above filter : On this I want to apply filter on Max(DateID).

Note: Maximum dateid for Mapid 12 is 634.

My requirement is:  Max DateID for MapID=12 which is 634.

Sum is: 1210 but its showing blank value

Thanks a lot @vcastello for replying. 

[v-ljerr-msft · Accepted solution]

Hi @mwadhwani,

First I want to filter f_WS[MapID]=12.
Subset obtained by above filter : On this I want to apply filter on Max(DateID).

Note: Maximum dateid for Mapid 12 is 634.

My requirement is:  Max DateID for MapID=12 which is 634.

Sum is: 1210 but its showing blank value

---

The formula below should work in this scenario. 

Current Ratio = 
VAR maxProcessDateID =
    CALCULATE ( MAX ( f_WS[ProcessDateID] ), f_WS[MapID] = 12 )
RETURN
    CALCULATE (
        SUM ( f_WS[Value] ),
        FILTER ( f_WS, f_WS[MapID] = 12 && f_WS[ProcessDateID] = maxProcessDateID )
    )

Regards

[vcastello]

Hi @mwadhwani

I've uploaded the modified .pbix file you uploaded before. Check it.

https://www.dropbox.com/s/kb8v28zfyfxlk8w/CheckThismodified.pbix?dl=0

The formula works because there is a filter context introduced by the slicer. It calculates the Maximum DateID for the [MapID] value indicated in the slicer and then sums the values for that DateID and [MapID]

Hope That Helps

Vicente

[mwadhwani]

Thanks @vcastello it indeed helped me. But I want to filter MapID=12 in DAX instead of using slicer for it.

But it can be used as work around.Thank You