DAX Measure to count only latest transaction

[mturcotte]

Hi,

I'm struggling with a fairly simple concept and I need help figuring out the DAX measure to count the Boolean value from the last transaction for every User_ID in the transaction log where all User_IDs start with "Access = False", but some later get granted access, i.e. "Access = True".

I managed to create a summary table (2nd from the right) listing the last transaction date for each User_ID.

But I'm still struggling with the DAX Measure that would count only "Access = False" when there are no subsequent "Access = True" at later dates for the same User_Id. Conversely, that measure would count "Access = True" and disregard preceeding "Access = False" for the same User_Id.

With such a measure the histogramme in the top center would have True = 15 and False = 5, because only 5 User_Id were never granted "Access = True" in the duration captured by the transaction log in Table.

I tried to share the link to the report from my OneDrive, but this editor does not allow me to create the link (OK button inactive) ...

 Does anyone know why the create link dialog has the OK button disabled after filling all the fields?

Thanks

MT

[v-eachen-msft · Accepted solution]

Hi @mturcotte ,

You could create the following three measures to get the result:

CountID =
VAR a =
    IF (
        SELECTEDVALUE ( 'Table'[Date] )
            = CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[User_Id] ) ),
        1,
        0
    )
RETURN
    COUNTX ( 'Table', a )

Status =
VAR a =
    IF (
        SELECTEDVALUE ( 'Table'[Date] )
            = CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[User_Id] ) ),
        1,
        0
    )
RETURN
    a

TotalCount = 
SUMX('Table',[Status])

Here is the result :

Here is my test file for your reference.

[mturcotte]

Thank you @v-eachen-msft !

Your solution works for me!

[mturcotte]

It even ripped the screenshot I had seemingly successfully inserted...

Trying again:

[mturcotte]

Still unable to share a link to either OneDrive or to GoogleDrive…

This forum is wonderful to search and find answers but TERRIBLE to post questions...

I also posted about a BUG on PBIRS May 2019 in the Report Server section and on Ideas (as a suggestion to fix it), yet it remains in both the September 2019 and the subsequent October 2019 release (that patched other bugs but not the one I had provided great details on)... MOST disappointing!

Anyway, should anyone want access to the Report mockup I created to illustrate my DAX challenge without compromising sensitive data, just send me an email at             mturcotte --at-- atip-solutions.com

Thanks!

[mturcotte_epq]

https://1drv.ms/u/s!ArclD4PLnaKAnxwSjb_r-cCDuUBJ?e=2W0ULd 

This time I succeeded in posting a link but using Chrome and connected from my account from customer's email system.

Go figure, but I'm still looking for help about the DAX measure to selectively count last boolean value and ignore preceeding ones for each user.

Thanks, MT