Measure with virtual tables where filter reference measure gives (partly) blank outcome

[ikavaju]

Hi,

My need is to report the Spend per Material Code so that only those material codes which have been bought at least two different countries during the selected time period are included. Material codes are country specific and the link between countries is the GTIN code, which connect country specific material codes to each other. I emphasize I need a measure, because calculation needs to be dynamic depending on selected time frame (calculated columns are not enough).  Simplified sample data is following:

Material Code		GTIN		Buying Country		Spend		Year
Material1111		EAN1234		SE		1000		2020
Material1118		EAN1234		DK		10000		2021
Material1114		EAN1235		DK		2000		2021
Material1121		EAN1235		DK		11000		2021
Material1117				DK		3000		2020
Material1124		EAN1236		DE		12000		2021
Material1120		EAN1237		DK		4000		2020
Material1127		EAN1237		SE		13000		2021
Material1123		EAN1238		DE		5000		2022
Material1113		EAN1238		SE		14000		2022
Material1126		EAN1239		DE		6000		2020
Material1116		EAN1239		DK		15000		2022
Material1130				SE		7000		2020
Material1119		EAN1240		DK		16000		2022
Material1112		EAN1241		SE		8000		2021
Material1122		EAN1241		DE		17000		2022
Material1115		EAN1242		DK		9000		2021
Material1125		EAN1242		DE		18000		2022
Material1128				SE		19000		2022
Material1129		EAN1253		SE		20000		2022

I have used two measure in addition to those columns.

1. NumOfCountries =

DISTINCTCOUNT('Table'[Buying Country])

2. Cross border GTINs Spend =

VAR Virtual_Table =

CALCULATETABLE(

ADDCOLUMNS(

VALUES('Table'[Material GTIN]),

"No_GTIN_Countires", [NumOfCountries]),

KEEPFILTERS('Table'[Material GTIN] <> blank()))

VAR vFilteredTable =

FILTER(

    Virtual_Table,

    [No_GTIN_Countires]>1)

RETURN

CALCULATE(

    SUM('Table'[Spend]), vFilteredTable

)

The result is that when I place Field GTIN and  measure Cross border GTINs Spend to the same table, I'll get the exected result, but when I add  column Material Code to same table all the values disappear, just showing blank table visualization. Same effect also if I have only Material Code and Cross border GTINs Spend on the same table visualization.
This should be doable, because I can manually calculate it in excel (the needed data exist), but the right use of DAX is mystery for me in this case.

I will appreciate your help and knowledge to solve the challenge.

[johnt75 · Accepted solution]

Try

Cross border GTINs Spend =
VAR Virtual_Table =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( 'Table'[Material GTIN] ),
            "No_GTIN_Countires", [NumOfCountries]
        ),
        KEEPFILTERS ( NOT ISBLANK ( 'Table'[Material GTIN] ) ),
        REMOVEFILTERS ( 'Table'[Material Code] )
    )
VAR vFilteredTable =
    FILTER ( Virtual_Table, [No_GTIN_Countires] > 1 )
RETURN
    CALCULATE ( SUM ( 'Table'[Spend] ), vFilteredTable )

[johnt75 · Accepted solution]

Try

Cross border GTINs Spend =
VAR Virtual_Table =
    CALCULATETABLE (
        ADDCOLUMNS (
            VALUES ( 'Table'[Material GTIN] ),
            "No_GTIN_Countires", [NumOfCountries]
        ),
        KEEPFILTERS ( NOT ISBLANK ( 'Table'[Material GTIN] ) ),
        REMOVEFILTERS ( 'Table'[Material Code] )
    )
VAR vFilteredTable =
    FILTER ( Virtual_Table, [No_GTIN_Countires] > 1 )
RETURN
    CALCULATE ( SUM ( 'Table'[Spend] ), vFilteredTable )

[ikavaju · Accepted solution]

Hi,

Thanks a lot @johnt75 !
That was in a way so simple and in the other hand so brilliant solution. Maybe I had a insufficient understanding of Removeflters. "Material code" was not even present in measure, so it was not so obvious for me.  This help a lot!