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
KBO
MVP

Show values only of the exist in specific columns

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

 

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

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

Screenshot 2021-03-07 031936.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

4 REPLIES 4
CNENFRNL
Community Champion
Community Champion

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

Screenshot 2021-03-07 031936.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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
Resolver III
Resolver III

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

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

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.

Top Solution Authors