topic Re: Flag of counting multiple sign ins in DAX Commands and Tips

Flag of counting multiple sign ins

mocha911 — Wed, 22 May 2024 03:18:03 GMT

I am trying to create a flag in the report to indicate which IP address has more than 1 member signing in within 3 consecutive days.

Data looks like -

Expected result -

yellow case - it obsiously has multiple members signing in , so a flag of 1

orange case - as there were more than 1 member signing in on 3/5 and 4/5, so a flag of 1

green case - as B signed in more than 3 days from A, so a flag of 0

Can you please help me with the DAX? thank you

Re: Flag of counting multiple sign ins

Jihwan_Kim — Wed, 22 May 2024 04:35:07 GMT

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

WINDOW function (DAX) - DAX | Microsoft Learn

expected result measure: = SUMX ( VALUES ( data[ip_address] ), CALCULATE ( IF ( COUNTROWS ( FILTER ( ADDCOLUMNS ( SUMMARIZE ( data, 'calendar'[Date] ), "@result", CALCULATE ( COUNTROWS ( SUMMARIZE ( data, data[member] ) ), WINDOW ( -2, REL, 0, REL, ALL ( 'calendar'[Date] ), ORDERBY ( 'calendar'[Date], ASC ) ) ) ), [@result] > 1 ) ) > 0, 1, 0 ) ) )

Re: Flag of counting multiple sign ins

mocha911 — Thu, 23 May 2024 03:46:21 GMT

Thank you so much for spending the time to do this! it works like a charm. I need to google to understand a couple of functions in there but wow....you are amazing! thank you