Forum Discussion

eddiechang's avatar
eddiechang
Helper I
8 years ago

Custom filter that applied for same fact table

Hi All,   I am a beginner for BI, wish to get help from expert to generate an expected outcome.   Generally the story is i want to analyze my supplier's purchases with combining the sales for tho...
eddiechang's avatar
eddiechang
Helper I
8 years ago

Add-on from my finding. Base on the sample gave to develop my data model. Still found out that those product that never purchased from this supplier would appear in listing.

 

Purchase =
CALCULATE (
SUM ( Fact_table[Amount] ),
Fact_table[TransactionType] = "purchase"
)

 

Sales =
CALCULATE (
SUM ( Fact_table[Amount] ),
Fact_table[TransactionType] = "sales",
ALL ( dbcr[dbcrName] )
)

 

 

 

Below is my data model.

 

 

Dunno why the productC will appear in outcome.

 

Thanks.

eddiechang's avatar
eddiechang
Helper I
8 years ago

Hi, Any expert or contributer can help.

Looking forward a helping hand.

Thanks in advance.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi eddiechang,

     

    Please try out the method below.

    1. Add a calculated column to identify if one product has previous purchase.

    IfPurchaseFromSup1 =
VAR firstSaleDate =
    CALCULATE (
        MIN ( 'Table1'[TransactionDate] ),
        ALLEXCEPT ( Table1, Table1[ProductCode] ),
        'Table1'[TransactionType] = "sales"
    )
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'Table1' ),
            ALLEXCEPT ( Table1, Table1[ProductCode] ),
            'Table1'[TransactionType] = "purchase",
            'Table1'[CustomerorSupplierCode] = "sup1"
        )
            > 0,
        "Y",
        "N"
    )

    2. Create a new measure like this.

    Sales 2 = 
CALCULATE (
    SUM ( Table1[Amount] ),
    Table1[TransactionType] = "sales",
    Table1[IfPurchaseFromSup1] = "Y",
    ALL ( Table1[CustomerorSupplierCode] )
)

    Custom_filter_that_applied_for_same_fact_table

     

    Best Regards,

    Dale

    • eddiechang's avatar
      eddiechang
      Helper I
      8 years ago

      Hi v-jiascu-msft,

       

      Thanks for your reply and appricieted!

      However, the requirement that i wish is the CustomerSupplierCode selection is dynamic by slicer selection. (single selection will do)

      Due to the user would select other supplier too.

       

      Thanks again.

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi eddiechang,

         

        The users can select other suppliers. Please try out it in this file.

         

        Best Regards,

        Dale