Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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] )
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! |
@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] )
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
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
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |