DAX ALLEXCEPT function with RELATED table columns as filters

[benabdillahr]

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,

[v-yanjiang-msft]

Hi @benabdillahr ,

According to your description, here's my solution.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@benabdillahr may be try this

CALCULATE( DISTINCTCOUNT(Visits[Visits: Visits Id]),
ALL('Date'),VALUES('Date'[Date]), ALL(Visits[Category]))

replicated on a small scale, pbix is attached

[benabdillahr]

Unfortunately, I get the same result ☹️

[smpa01]

@benabdillahr  please provide a sample pbix representative of the issue

[benabdillahr]

Hi @smpa01 !

Here is exactly what I'm looking for, i just need the date filter to come from the date table instead of the visits table.

[smpa01]

@benabdillahr  please check page2

[smpa01]

@benabdillahr  did you have a chance to look into the solution I provided?

[smpa01]

@benabdillahr can you test out if this works for you

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

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

[benabdillahr]

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!