Forum Discussion

karyani's avatar
karyani
Frequent Visitor
1 year ago
Solved

Dax All not ignoring filter

the model has only 2 table FactTable and dimintions 

the measure bellow  works as expected, the calculation ignores the dimintion, (in a slicer)

calc = CALCULATE(DISTINCTCOUNT(FactTable[TransactionCode])
                ,All(Dimintion)))
  
if I add a filter on the FactTable it stops ignoring the Diminsion!
 
calc = CALCULATE(DISTINCTCOUNT(FactTable[TransactionCode])
                ,All(Dimintion)
                ,FILTER(FactTable,FactTable[CancelXID] <> BLANK())
                )
after adding the above filter on the FactTable the All(Dimintion) stops working. when selecting differnt values from the slicer the calculation changes.  what am I missing here, and how do I write the measure to ignore the Diminsion and still apply the ,FILTER(FactTable,FactTable[CancelXID] <> BLANK()?
 
thank you
  • sjoerdvn's avatar
    sjoerdvn
    1 year ago

    try something like 

    calc = CALCULATE(DISTINCTCOUNT(FactTable[TransactionCode])
                ,All(Dimintion)
                ,FactTable[CancelXID] <> BLANK()
       )

or even

calc = CALCULATE(DISTINCTCOUNT(FactTable[TransactionCode])
                ,All(Dimintion)
                ,FactTable[CancelXID] 
       )

4 Replies

  • sjoerdvn's avatar
    sjoerdvn
    Icon for Solution Sage rankSolution Sage
    1 year ago

    try something like 

    calc = CALCULATE(DISTINCTCOUNT(FactTable[TransactionCode])
                ,All(Dimintion)
                ,FactTable[CancelXID] <> BLANK()
       )

or even

calc = CALCULATE(DISTINCTCOUNT(FactTable[TransactionCode])
                ,All(Dimintion)
                ,FactTable[CancelXID] 
       )
    • karyani's avatar
      karyani
      Frequent Visitor
      1 year ago

      The fist option seems to work :), so why Filter does not work?

      • sjoerdvn's avatar
        sjoerdvn
        Icon for Solution Sage rankSolution Sage
        1 year ago

        The FILTER expression is evaluated in the "default" context so it includes any filter on "Dimintion" (sic). If you really want to use FILTER (don't know why people are using filter all the time...) you could use something like:

         

        calc = CALCULATE(DISTINCTCOUNT(FactTable[TransactionCode])
                ,All(Dimintion)
                ,FILTER(CALCULATETABLE(FactTable,All(Dimintion)),FactTable[CancelXID] <> BLANK())
       )