Show values only of the exist in specific columns

[KBO]

Hi together,

I have a little DAX problem and as I mentioned before I'm no DAX crack XD.

So my problem today is:

	Germany	France	Spain	Japan
Customer A	1	2	3	2
Customer B	2		2
Customer C	1	2	2	3

Show values only If a Customer have a Sales in Germany and Spain but not If they sold in another country too.

Expected result:

	Germany	France	Spain	Japan
Customer B	2		2

Maybe this problem is easy but today is not my day :).

Best,

Kathrin

[CNENFRNL · Accepted solution]

@KBO , here's a measure solution for your reference,

Coverage = 
VAR __c =
    CALCULATETABLE (
        DISTINCT ( Sales[Country] ),
        ALLEXCEPT ( Sales, Sales[Customer] )
    )
VAR __filtered = FILTER ( __c, Sales[Country] IN { "Germany", "Spain" } )
RETURN
    IF ( COUNTROWS ( __c ) = COUNTROWS ( __filtered ), [Total] )

[KBO]

Hi @CNENFRNL ,

thats a good solution! Now I only need to filter out all of the others but that's easy thanks!

Best,

Kathrin

[rfigtree]

mSales=sum(sales)

mAllCountrySales=calculate([mSales],all(Table[Country]))

mSalesSpainOrGermany=calculate([mSales],filter(all(table[Country]),table[Country]="Spain" || table[Country]="Germany"))

mSalesSpainOrGermanyOnly=if([mAllCountrySales]=[mSalesSpainOrGermany],[mSales],blank())

[KBO]

Hi @rfigtree ,

This dosn't work for my case ... I need a NOT IN from SQL into DAX but finally I solved my problem 😄 with visuals and filters 😉

But Thanks for helping.

Best,

Kathrin