filtering data in table visual using DAX

[aatish178]

hi all, I have below table visual

ShopNo	City	SupplierNo	PurchaseDate	Item Qty
SH01	London	19197	2024-02-25	20
SH01	London	15346	2024-02-25	20
SH01	London	19720	2024-02-25	10
SH01	London	22310	2022-11-23	5
SH01	London	19720	2023-02-04	15
SH01	London	19197	2021-03-07	3
SH01	London	19197	2021-07-01	2
SH01	London	19197	2020-12-12	8

For above data, I want to show user a latest only one SupplierNo based on below scenarios:

Scen 1: from above data, for each ShopNo, City unique combination, find out what is the max/latest PurchaseDate.

Here max purchase date is 2024-02-25, which is shared by 3 suppliers. Hence check scen 2:

Scen2: from the result of max purchase date again filter that result for each ShopNo, City and Max purchase date, find out what is the max of Item Qty., here max item qty is 20, which is again shared by 2 suppliers hence last 3rd scen

Scen3: From the result that we got so far, check what is the max supplier no for ShopNo, City, Max purchase date and Max ItemQty ., and show that supplier as "Latest Supplier", here 19197 will be final and latest supplier which has latest PurchaseDate and latest ItemQy.

Can someone please help, how to write a dax query for this? @quantumudit @Ashish_Mathur @Anonymous 

Aatish

[aatish178]

Hi All, can someone please help?

Aatish

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[aatish178]

Hi Ashish, thanks for your reply , but I dont want to use the solution it in filter option. I want to create a nested agreegation expression , which will filter one supplier for Average of Item Qty.

[gmsamborn]

Hi @aatish178 

Would a measure like this help?

z = 
VAR _LatestDate =
    CALCULATE(
        MAX( 'Table'[PurchaseDate] ),
        ALL( 'Table'[PurchaseDate] )
    )
VAR _MaxQty =
    CALCULATE(
        MAX( 'Table'[Item Qty] ),
        'Table'[PurchaseDate] = _LatestDate
    )
VAR _MaxSupplier =
    CALCULATE(
        MAX( 'Table'[SupplierNo] ),
        'Table'[PurchaseDate] = _LatestDate
            && 'Table'[Item Qty] = _MaxQty
    )
RETURN
    _MaxSupplier

I might need more details on HOW you want to use this measure for filtering.

Let me know if you have any questions.

[aatish178]

Hi gmsamborn,

Thanks for your reply, however the expectation is to use Nested aggrgation of each step in order to get final average of ItemQty., Here the expression you provided dont have group  by with ShopNo and City.

The final result should contain only single(should show Avg of Item Qty) with max Supplier No having max of PurchaseDate and Max of ItemQty., Here basically we have to filter the data for each step.

In Laymen term the expression should be:

if(Max(PurchaseDate) group by ShopNo and City is equal to Purchase Date then calculate Max of ItemQty group by ShopNo, City and Max of Purchase date. 

If that max of ItemQty=ItemQty then calculate Max of supplierNo group by ShopNo, city, Max of Purchase Date, Max of Item Qty

If this results equals to Supplier No., then finally show Average of Item Qty as a final result

[aatish178]

As soon as I use this final expression in table visual along with other attributes, I should get below single row as a result

here 20 is the Average of Item Qty that i have calculated for each ShopNo, city unique combination having Max of PurchaseDate, Max of Item Qty and Max of Supplier No.

[aatish178]

@gmsamborn plz check once