DAX measure filter is not working

[Spartanos]

I would like to combine two tables into one, per ticker. I have multiple tickers in table source, table_X has just one ticker.

Next, the amont of rows are not identical (table X has more rows), and cause problems with calculating the average price per month. 

The left table should have the same output as the two tables next to them. The goal is to calculate the average price per month, for ticker FBX1 (table source), and FBX1 (Table_X), whereby the rows without a price should not be taking into account.

Please see the pbit file: https://drive.google.com/file/d/1GNSvRt2J1KSIKdbIlk6JhM03gkRNjLLs/view?usp=sharinghttps://drive.goog...

I have this code, but it look likes that the filter for only FBX1 is not working.

Combined =

VAR _source =

SELECTCOLUMNS(

    ADDCOLUMNS(

        CALCULATETABLE(

            VALUES(source[Month]),

            source[Ticker] = "FBX1"

        ),

        "AvgSource", CALCULATE(AVERAGE(Source[Price]))

    ),

    "Month", source[Month] & "",

    "AvgSource", [AvgSource]

)

VAR _X =

SELECTCOLUMNS(

    ADDCOLUMNS(

        VALUES(Table_X[Month]),

        "AvgFB", CALCULATE(AVERAGE(Table_X[Price]))

    ),

    "Month", Table_X[Month] & "",

    "AvgFB", [AvgFB]

)

VAR _result = NATURALLEFTOUTERJOIN(_X, _source)

RETURN

    _result

[tamerj1 · Accepted solution]

Hi @Spartanos 
Here is the sample file with the solution https://www.dropbox.com/t/Hmqitl19cf26Q9V8

Combined2 = 
VAR _source = 
    ADDCOLUMNS (
        FILTER (
            source2,
            source2[Ticker] = "FBX1" && NOT ISBLANK ( Source2[Price] )
        ),
        "@Month", DATE ( YEAR ( source2[Month] ), MONTH ( Source2[Month] ), 1 )
    )
VAR SummarySource =
    SUMMARIZE ( _source, [@Month], "@AvgSource", AVERAGE ( Source2[Price] ) )
VAR _X = 
    ADDCOLUMNS (
        Table_X2,
        "@Month", DATE ( YEAR ( Table_X2[Month] ), MONTH ( Table_X2[Month] ), 1 )
    )
VAR SummaryX =
    SUMMARIZE ( _X, [@Month], "@AvgFB", AVERAGE ( Table_X2[Price] ) )
VAR Result = 
    NATURALLEFTOUTERJOIN ( SummarySource, SummaryX )
RETURN
    Result

[v-yalanwu-msft · Accepted solution]

Hi, @Spartanos ;

You could create a table 

Newtable1 = 
var _month=UNION(VALUES('Source'[Month]),VALUES('Table_X'[Month]))
return SUMMARIZE(_month,[Month],
"AvgFB",CALCULATE(AVERAGE(Table_X[Price]),FILTER('Table_X',[Month]=EARLIER([Month]))),
"AvgSource",CALCULATE(AVERAGE(Source[Price]),FILTER('Source',[Ticker]="FBX1"&&[Month]=EARLIER([Month]))))

The final output is shown below:

Or 

Newtable2 = 
var _month= UNION(VALUES('Source'[Month]),VALUES('Table_X'[Month]))
var _yearmon=SUMMARIZE( SUMMARIZE(_month,[Month],"Y-Month",EOMONTH([Month],0)),[Y-Month])
return 
SUMMARIZE(_yearmon,[Y-Month],
"AvgFB",CALCULATE(AVERAGE(Table_X[Price]),FILTER('Table_X',EOMONTH( [Month],0)=EARLIER([Y-Month]))),
"AvgSource",CALCULATE(AVERAGE(Source[Price]),FILTER('Source',[Ticker]="FBX1"&&EOMONTH( [Month],0)=EARLIER([Y-Month]))))

The final output is shown below:

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

[tamerj1 · Accepted solution]

Hi @Spartanos 
Here is the sample file with the solution https://www.dropbox.com/t/Hmqitl19cf26Q9V8

Combined2 = 
VAR _source = 
    ADDCOLUMNS (
        FILTER (
            source2,
            source2[Ticker] = "FBX1" && NOT ISBLANK ( Source2[Price] )
        ),
        "@Month", DATE ( YEAR ( source2[Month] ), MONTH ( Source2[Month] ), 1 )
    )
VAR SummarySource =
    SUMMARIZE ( _source, [@Month], "@AvgSource", AVERAGE ( Source2[Price] ) )
VAR _X = 
    ADDCOLUMNS (
        Table_X2,
        "@Month", DATE ( YEAR ( Table_X2[Month] ), MONTH ( Table_X2[Month] ), 1 )
    )
VAR SummaryX =
    SUMMARIZE ( _X, [@Month], "@AvgFB", AVERAGE ( Table_X2[Price] ) )
VAR Result = 
    NATURALLEFTOUTERJOIN ( SummarySource, SummaryX )
RETURN
    Result

[Spartanos]

Please use : https://drive.google.com/file/d/1GNSvRt2J1KSIKdbIlk6JhM03gkRNjLLs/view?usp=sharing

[tamerj1]

@Spartanos 

Hello again. It seems you've missed my last reply. 
based on the provided sample data, kindly provide the expected result table that you are looking for. 
thank you

[tamerj1]

Hi @Spartanos 

can you please advise the expected outcome?

[Spartanos]

the outcome for source FBX1 (ticker) should be 10, for the Table_X, the average should be 12,33

[tamerj1]

@Spartanos 

Not clear. please make the 'Combined' the table in excel for example and share a screenshot

[Spartanos]

The calculation should be as follows:  
Table Source (I am only interested in FBX01, and if a price is not egual to 0, hence 10+10+10/3=10. For table Table_X it is just the average, there are now missing values, and no need for filtering in the ticker. Hence, 10+11+12+13+14+14/6=12,33.

Month		Source (FBX1)	Table_X
2022-01		10	12,33

[tamerj1]

Hi @Spartanos 
Sahred file is deleted from source. Please upload and share again. Thank you

[Spartanos]

Hi, the file is uploaded again.

[tamerj1]

Hi @Spartanos 
You gave the permission for view only. I cannot download and of course cannot view a PBI file online