ALL or ALLEXCEPT to include/exclude filters from measure?

[niko18033]

I need some assistance repairing my formula to calculate rate in a table which has two visual-level filters. I have a year's worth of data loaded into the dataset, but would like this table/graph to only display the latest 60 days. I also want to exclude 2 categories from the numerator ('Count'), but include them in the denominator ('Total Count'). Currently, I'm doing this exclusion through a visual-level filter in the left table in the below example.

Rate = DISTINCTCOUNT(Table[Field_ID]) 
/ 
CALCULATE(DISTINCTCOUNT(Table[Field_ID]), ALL(Table[Category]))

The formula is working fine, until I filter to the latest 60 days. How do I incorporate this within my formula to account for this? Do I need to add another condition for 'Day' in ALL or perhaps ALLEXCEPT or FILTER?

In the example below, 'Rate' (left table) should equal 'Count' / 'Total Count' (from the right table). The first category with Count = 6351 and Total Count = 37839 should have Rate = 16.8%. However, the Rate is showing up as 35.5%.

When I remove the filter for latest 60 days, I get the proper rate of 16.8%. 

Any assistance in updating my formula would be much appreciated! Please let me know if you need me to provide any further details.

[AntrikshSharma]

@niko18033 Let me know if this works for you.

Total Count =
DIVIDE (
    DISTINCTCOUNT ( Table[Field_ID] ),
    CALCULATE (
        DISTINCTCOUNT ( Table[Field_ID] ),
        ALL ( Master ),
        VALUES ( Master[Day] ),
        VALUES ( Master[Client] )
    )
)

[amitchandak]

@niko18033 , One option is all selected

CALCULATE(DISTINCTCOUNT(Table[Field_ID]), ALLSELECTED(Table[Category]))

But deal better always have different tables for Master/Dimension like Date, Category, etc

CALCULATE(DISTINCTCOUNT(Table[Field_ID]), ALL(Category)), will remove Category and Date will continue to work. So better create different tables for parameters.

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

refer : https://youtu.be/kU2M1LmNvNo

https://youtu.be/vHuhbvYCiNc

[Greg_Deckler]

@niko18033 Try using ALLEXCEPT instead of ALL. There is also KEEFILTERS and REMOVEFILTERS

[niko18033]

Thanks for the quick response @Greg_Deckler . I tried ALLEXCEPT as well, with 'Day' as the Column Name filter. It now properly displays the rate and accounts for the date filter, but now if I filter to a particular client (using a slcier), the total count now ignores the Client slicer. I want the 'Client' slicer to still operate (not be ignored) and change the values for 'Count' and 'Total Count'

Total Count = DISTINCTCOUNT(Table[Field_ID])
/
CALCULATE(DISTINCTCOUNT(Table[Field_ID]),
ALLEXCEPT(Master,Master[Day]))

How can I adjust the formula to not ignore any other filters, such as 'Client'?

[Greg_Deckler]

@niko18033 - Maybe:

Total Count = DISTINCTCOUNT(Table[Field_ID])
/
CALCULATE(DISTINCTCOUNT(Table[Field_ID]),
ALLEXCEPT(Master,Master[Day],Master[Client]))

Otherwise, will need to see some sample data and expected output. (sample data as text)