Forum Discussion

mohammedismail's avatar
mohammedismail
Icon for Helper I rankHelper I
7 years ago
Solved

Identifying New Suppliers

Hi,

 

I have a DAX which is getting me the count of the new Suppliers within a certain time frame.

 

I want to edit this DAX to get me the List of those new Suppliers. I want to know which are those new suppliers.

 

I believe it is a small tweak that is needed in the DAX replacing the 'countrows'

 

Can someone help please ?

 

New Supplier Count =
var SupplierTM = VALUES(PO_Spend[Supplier ID])
var priorsuppliers = CALCULATETABLE(VALUES(PO_Spend[Supplier ID]),
FILTER(ALL(PO_Spend),
PO_Spend[Ordered Date] > MIN(PO_Spend[Ordered Date]) - [No of Days Value] &&
PO_Spend[Ordered Date] <= MIN(PO_Spend[Ordered Date])))
Return
COUNTROWS(
EXCEPT(SupplierTM,priorsuppliers))
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, mohammedismail

        Claculate table can't be affected by slicer, it works Dynamically only for the data source.

     

    when you refresh data, and table1 or table2 data has changed then the calculate table will change dynamically.

    and for your requirement, you can just add  Supplier ID and the New Supplier Count into the table visual.

    for example:

    I use this measure

    Measure = var _SupplierTM=VALUES(Table1[ID])
var _priorsuppliers=CALCULATETABLE(VALUES(Table2[ID]),FILTER(Table2,Table2[Index]<=Parameter[Parameter Value])) return
VAR _TABLE =EXCEPT ( _SupplierTM, _priorsuppliers ) RETURN
COUNTROWS(
_TABLE)

    then slicer is 0

    and slicer is 3

    Best Regards,

    Lin

3 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, mohammedismail

         You can just use this formula to add a calculate table to achieve your requirement.

    New Supplier List =
VAR SupplierTM =
    VALUES ( PO_Spend[Supplier ID] )
VAR priorsuppliers =
    CALCULATETABLE (
        VALUES ( PO_Spend[Supplier ID] ),
        FILTER (
            ALL ( PO_Spend ),
            PO_Spend[Ordered Date]
                > MIN ( PO_Spend[Ordered Date] ) - [No of Days Value]
                && PO_Spend[Ordered Date] <= MIN ( PO_Spend[Ordered Date] )
        )
    )
RETURN
    EXCEPT ( SupplierTM, priorsuppliers )

    For example:

    this is all Suppliers table

    and this is old Suppliers 

    then use this formula

    New List = var _SupplierTM=VALUES(Table1[ID])
var _priorsuppliers=VALUES(Table3[ID]) return
EXCEPT ( _SupplierTM, _priorsuppliers )

    Result:

    Best Regards,

    Lin

     

     

     

    • mohammedismail's avatar
      mohammedismail
      Icon for Helper I rankHelper I
      7 years ago

      Hi v-lili6-msft

       

      I added the below code in the New Calculated Table which returned the list of suppliers. But this isn't working Dynamically as expected. I have the 'No of Days Value' coming from What if Parameter I don't see the Supplier list change when I change the numbers from the Slider.

       

      Appreciate your help.

       

      New Supplier List =
VAR SupplierTM =
    VALUES ( PO_Spend[Supplier ID] )
VAR priorsuppliers =
    CALCULATETABLE (
        VALUES ( PO_Spend[Supplier ID] ),
        FILTER (
            ALL ( PO_Spend ),
            PO_Spend[Ordered Date]
                > MIN ( PO_Spend[Ordered Date] ) - [No of Days Value]
                && PO_Spend[Ordered Date] <= MIN ( PO_Spend[Ordered Date] )
        )
    )
RETURN
    EXCEPT ( SupplierTM, priorsuppliers )
      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        hi, mohammedismail

            Claculate table can't be affected by slicer, it works Dynamically only for the data source.

         

        when you refresh data, and table1 or table2 data has changed then the calculate table will change dynamically.

        and for your requirement, you can just add  Supplier ID and the New Supplier Count into the table visual.

        for example:

        I use this measure

        Measure = var _SupplierTM=VALUES(Table1[ID])
var _priorsuppliers=CALCULATETABLE(VALUES(Table2[ID]),FILTER(Table2,Table2[Index]<=Parameter[Parameter Value])) return
VAR _TABLE =EXCEPT ( _SupplierTM, _priorsuppliers ) RETURN
COUNTROWS(
_TABLE)

        then slicer is 0

        and slicer is 3

        Best Regards,

        Lin