Exclude user with condition from the chart

[hangbtt2]

Hi everyone,

I have a problem that I hope you can help me with. I have user data tracked by events. I want the chart to filter out users who have at least one event_type=1 within a specified time period.

However, when I add this measure to a chart with the Y-axis as Month and Year, it doesn't work.

Here is the measure I wrote:

actions_by_user_not_having_event1 =

var cte = filter (SUMMARIZE( fact_event, fact_event[user_id],

                                                "MinEventType", CALCULATE(MIN(fact_event[event_type]), ALLSELECTED('Date'[Date])) ,

"events", COUNTROWS(fact_event) ), [MinEventType] > 1) return SUMX(cte, [events])

Thank you guys

[MattAllington · Accepted solution]

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

[MattAllington · Accepted solution]

I have deployed what I suggested.  It appears to work to me.

https://www.dropbox.com/scl/fi/lg6xta1fr9magjehf3cfq/not_event_type_1.pbix?rlkey=e1ri52nt9xjk1yjdq8k...

[hangbtt2]

Thank you so much for your help Matt.

[MattAllington · Accepted solution]

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

[MattAllington]

See my previous point

"You can't solve this problem (reliably) without a user table."

create a user table, then it should work. 

[hangbtt2]

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 )

)

[hangbtt2]

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

[hangbtt2]

Hi Matt, Thank you so much for your promt response.

I dont think it works.

Here is the link to my dataset: https://drive.google.com/drive/folders/1sLpG6WckCVyl4nEjADqw3V8KukFb-sd8?usp=sharing

Thank you so much

[MattAllington]

I understand that DAX can be hard to learn. It's especially deceptive if you have a SQL background; most have to unlearn some concepts from SQL in order to become aware of the right approach in DAX.

As I understand the problem, you want a count of events, but exclude all events from users that have at least one event 1. Once you see a working solution, it's easy. "How" to think about this problem with respect to DAX is the real challenge. 

You don't mention if you have a star schema. If you don't, you need to get one. You can't solve this problem (reliably) without a user table.  

I don't know if this will work as I don't have any test data. If it doesn't, it should be close. 

= CALCULATE (
COUNTROWS ( fact_event ),
FILTER ( User, CALCULATE ( MIN ( factevent[eventtype] ) > 1 ) )
)