Forum Discussion

benabdillahr's avatar
benabdillahr
Frequent Visitor
4 years ago

DAX ALLEXCEPT function with RELATED table columns as filters

Hi All,

I am trying to calculate the distinct count of a field "Visits" but only need it to be filtreable by a "Date" field from another table.
I used the ALLEXCEPT funtion but the filter and formula only work when the "Date" field from the same table, when I use the "Date" field from the "Date" table, it doesn't work. 

Same Table Formula:

Total visits = CALCULATE( DISTINCTCOUNT(Visits[Visits: Visits Id]),
ALLEXCEPT(Visits, Visits[Date]))

 

Related Tables Formula:
Total visits = CALCULATE( DISTINCTCOUNT(Visits[Visits: Visits Id]),
ALLEXCEPT('Date','Date'[Date])

 


Thanks,



Need Help

9 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    benabdillahr can you test out if this works for you

    CALCULATE( DISTINCTCOUNT(Visits[Visits: Visits Id]),

    ALL('Date'),VALUES('Date'[Date]))

     

     

    • benabdillahr's avatar
      benabdillahr
      Frequent Visitor
      4 years ago

      Thank you smpa01 for you response!
      I tried your formula, the date filter from the related table is now working but it calculates the distinct count of visits by category. I want my measure to ignore the categories in order to have the same value "243" repeated for each category since I'm using it as a denominator for another measure.

      Thanks!