Forum Discussion

allenpee85's avatar
allenpee85
Helper 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 ...
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.

allenpee85's avatar
allenpee85
Helper 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
Community 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.