Forum Discussion

allenpee85's avatar
allenpee85
Icon for Helper I rankHelper I
6 years ago
Solved

Filter in Another Table is incorrect

Dear Community,

 

I have 2 tables seems like not linked correctly.

 

1st table - I will select the one of Customers Current vs Previous OS

2nd table - Help me determine which product contribute to the largest OS movement, based on 1st table (this table has problem)

 

Please see my code and image for easy reference.

 

Previous OS

Previous Month OS =
VAR _MinMonth =
     CALCULATE ( MIN( 'Date'[Month] ), ALLSELECTED())
RETURN
     CALCULATE (
          SUM(Combine[OS]),
               FILTER(ALL('Date'[Month]),'Date'[Month]=_MinMonth))

 

Current OS

Current Month OS =
VAR _MaxMonth =
     CALCULATE ( MAX( 'Date'[Month] ), ALLSELECTED())
RETURN
     CALCULATE (
          SUM(Combine[OS]),
               FILTER(ALL('Date'[Month]),'Date'[Month]=_MaxMonth))

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi , allenpee85 

    Sorry for late reply.

    You need to create calendar table.

    Here is  a demo .

    pbix attached

     

    If help ,try steps as below.

    1.create a calendar table .

    Date = SELECTCOLUMNS(DISTINCT('Sales'[Month]),"Date",[Month])

    2.using 'Date'[Date] instead of 'Sales'[Month] in measures

    Current Month OS = 
VAR _MaxMonth =
    CALCULATE (MAX('Date'[Date]))
RETURN
    CALCULATE(
        SUM(Sales[OS]),
        FILTER(ALLSELECTED(Sales[Month]),Sales[Month]=_MaxMonth))
    Previous Month OS = 
VAR _MinMonth =
    CALCULATE (MIN('Date'[Date]), ALLSELECTED())
RETURN
    CALCULATE(
        SUM(Sales[OS]),
        FILTER(ALL(Sales[Month]),Sales[Month]=_MinMonth))

     And it will show as below:

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi! 

    Would you please post a picture of just maxmonth_ in the matrix that is problematic?

    Current Month OS = 
VAR _MaxMonth =
     CALCULATE ( MAX( 'Date'[Month] ), ALLSELECTED())
RETURN
_MaxMonth


    Also, the relationship between 'Combine' and 'Date' is it a double-crossfilter?

    Br,
    J

    • allenpee85's avatar
      allenpee85
      Icon for Helper I rankHelper I
      6 years ago

      Hi v-easonf-msft , tex628 ,

       

      Sorry to keep you waiting.

       

      I have created a sample file that exactly described my current situation for your kind investigation.

       

      Raw Data - Below Filter result zero OS.

      - Month (Feb-2020)

      - Country (USA)

      - Industry (Bike)

       

       

      However, in PBI, I filtered the same as I did in Excel, both tables showing unexpected results.

       

      Below is the link of my sample file.

      https://drive.google.com/drive/folders/1lFnkRZARhoxmWNqXbOGAswhEnIQICmmH?usp=sharing 

       

      Thanks for your help.

       

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi , allenpee85 

        Sorry for late reply.

        You need to create calendar table.

        Here is  a demo .

        pbix attached

         

        If help ,try steps as below.

        1.create a calendar table .

        Date = SELECTCOLUMNS(DISTINCT('Sales'[Month]),"Date",[Month])

        2.using 'Date'[Date] instead of 'Sales'[Month] in measures

        Current Month OS = 
VAR _MaxMonth =
    CALCULATE (MAX('Date'[Date]))
RETURN
    CALCULATE(
        SUM(Sales[OS]),
        FILTER(ALLSELECTED(Sales[Month]),Sales[Month]=_MaxMonth))
        Previous Month OS = 
VAR _MinMonth =
    CALCULATE (MIN('Date'[Date]), ALLSELECTED())
RETURN
    CALCULATE(
        SUM(Sales[OS]),
        FILTER(ALL(Sales[Month]),Sales[Month]=_MinMonth))

         And it will show as below:

        Best Regards,
        Community Support Team _ Eason
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.