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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ikavaju
Frequent Visitor

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

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.
2 ACCEPTED SOLUTIONS
johnt75
Super User
Super User

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 )

View solution in original post

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!

 

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

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 )

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!

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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