Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
allenpee85
Helper I
Helper I

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.

Untitled.png

 

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))

 

1 ACCEPTED SOLUTION

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:

80.png

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.

View solution in original post

4 REPLIES 4
v-easonf-msft
Community Support
Community Support

Hi , @allenpee85 

Can you show me the  model relationship view here?

06.png

 

Best Regards,
Community Support Team _ Eason

 

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)

Untitled.jpg

 

 

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

Untitled 1.jpg

 

Below is the link of my sample file.

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

 

Thanks for your help.

 

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:

80.png

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.

tex628
Community Champion
Community Champion

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


Connect on LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.