Forum Discussion
Exclude user with condition from the chart
- 2 years ago
It looks to me like you are using Date from the fact table in your formula and your visual (possibly the slicer, too). This is not the correct way to do it.
- Your calendar table dimension table should contain all columns you need for reporting. https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/
- Thou should not use the date column in your fact table in a visual (there are some exceptions). I suggest you hide it. Same with the user ID in the fact table.
I missed the original point you made about the "selected period". You will need something like this (after you fix the calendar issues). I assume what you want is for the slicer to select the period and for the formula to remove anyone with the event =1 for the slicer period, not the monthly periods in the column chart. If that's not what you want, then this formula will need to change
= CALCULATE (
COUNTROWS ( fact_event ),
FILTER ( User, CALCULATE ( MIN ( factevent[eventtype] ) > 1 ) ),ALLSELECTED(Date)
)it may be that you need this, but I can't be sure. You would have to test both.
= CALCULATE(CALCULATE (
COUNTROWS ( fact_event ),
FILTER ( User, CALCULATE ( MIN ( factevent[eventtype] ) > 1 ) ))ALLSELECTED(Date)
)also, do yourself a favour and get rid of the chart until it's working. Set up a matrix and add the same columns (month, event type). It is much easier to debug the problems if you can see the numbers.
after these changes, if you still can't sort it, post the workbook again and I will take a look. I will be at my desk in about 1 hour
- 2 years ago
I have deployed what I suggested. It appears to work to me.
See my previous point
"You can't solve this problem (reliably) without a user table."
create a user table, then it should work.
- hangbtt22 years agoFrequent Visitor
Hi Matt,
Thank you again with very quick response.
I follow you instruction but it didnt work yet. I think it is somehow affected by month-year filter context in the chart. Could you help me take a look at my dataset?
https://drive.google.com/drive/folders/1sLpG6WckCVyl4nEjADqw3V8KukFb-sd8?usp=sharing
- hangbtt22 years agoFrequent Visitor
Hi Matt,
I followed your instruction and it works. But the thing is when event_type is added to the chart as legend, the measure goes wrong (without the legend, it works perfectly)
test = CALCULATE (COUNTROWS ( data ),FILTER ( User, CALCULATE(MIN(data[event_type]), ALLSELECTED('date'[Date].[Month], 'date'[Date].[MonthNo]))> 1 ))