Forum Discussion
Filter problems
Hi Anonymous ,
Try this measure:
_Measure = VAR D = [Value] -- The current value based on filters VAR AD = CALCULATE( [Value], ALLEXCEPT('FactTable', 'FactTable'[Other Columns...]) -- Preserve filters on other columns except Status ) VAR RESULT = DIVIDE(D, AD, BLANK()) -- Calculate the percentage RETURN RESULTIf the above dont work, please consider to try this one with SUMX:
_Measure = VAR D = [Value] VAR AD = SUMX( ALL('FactTable'[Status]), CALCULATE(SUM('FactTable'[Values])) ) VAR RESULT = DIVIDE(D, AD, BLANK()) RETURN RESULT
So this is a simplifyed version of my fact table:
| date_id | warehoue_id | Status | Days | |
| 20240101 | 1 | Sent | 1 | |
20240102 |
| 1 | Sent | 1 |
| 20240101 | 2 | packed | 1 | |
| 20240102 | 2 | Sent | 1 | |
| 20240101 | 1 | Sent | 1 | |
| 20240102 | 1 | Sent | 1 | |
| 20240101 | 2 | packed | 1 | |
| 20240102 | 2 | Sent | 1 | |
| 20240101 | 1 | Sent | 1 | |
| 20240102 | 1 | Sent | 1 |
My goal is to have a measure that show days on a status vs days on all statuses in selected filter context.
So with this filtering
date = 20240101
Status = Sent
Measure should give 60% ( 3 days on packed & 2 days on Sent = 5 days. 3/5= 0.6 60%)
Or with filter:
Warehouse = 2
Status = Packed
Measure should give 50% ( 2 days on packed & 2 days on Sent = 4 days. 2/4= 0.5 50%)
My problem is to get measure to ignore filter on status and return all days for date & warehouse filter context.
Ok I got it to work, looks like this
_TEST =
VAR D = [Days]
VAR AD =
CALCULATE (
[Days],
ALLEXCEPT ( 'factTable', 'factTable'[Warehouse_id], 'Date'[Date_id] )
)
VAR RESULT =
DIVIDE ( D, AD, BLANK () )
RETURN
RESULT
If I replace [Days] in VAR AD with D measures stops giving me all rows for Status.
Any ways it works, big thanks for all help and input 🙏
- Bibiano_Geraldo
Super User
Hi Anonymous ,
Happy it works.