hello,
I have a table with many many columns (needed for other reasons), but in most of my measures, I only need a few columns: Case ID, created date, closed date, period date (snapshot date. always last day of the month), status.
so I would like to learn how to use only the columns needed (for performance and understaning of DAX reasons). Here is what I know:
1. ALL(<column1>, <column2>, etc) creates a temporary table with all the already existing combinations of values in called columns.
2. This measure gives me the correct result:
CALCULATE(
DISTINCTCOUNTNOBLANK(Fact_table[ case id])
, Fact_Table[status] = "CL" -- a case without a closed date cannot have this status per definition
, KEEPFILTERS( Fact_Table[Created date] <= Fact_Table[Period date]) -- a case is only closed if within reporting period if it was created before or in this period
, KEEPFILTERS(Fact_Table[Closed date] <= Fact_Table[Period date] && Fact_Table[Closed date] >= date( year(Fact_Table[Period date]), MONTH(Fact_Table[Period date]), 01)) -- a case is only closed in the reporting period if the closed date is within the reporting period
)
users select a period date (from date table, 1-to-many active relationship based on period date). This measure calculates the number of closed cases within the reporting period (the month of period date). a case can only be closed in that period if it has been created before or during the period (so future cases don't count. don't ask why this is an issue. DQ), and closed within the period.
so the question is, how do I combine my piece of knowledge #1 and my piece of knowledge #2? or in other words, how can I re-write the measure to only use the columns actually needed instead of a whole table?
EDIT: just to clarify, I'm using KEEPFILTERS because if I remove that part, then in a table visual with the following columns [entity name, period date, created date, closed date, measure] I get the same value on every single row... which is not correct. I don't understand this either.
