Forum Discussion

Mi_80's avatar
Mi_80
Icon for Helper I rankHelper I
3 years ago
Solved

Update table and last record by category

Hi, I am trying to produce something similar but this is not working for me at all .  I am told index is not a function?

 

I have an updates table that update a user status from approved, not approved in categories one and category 2. I also have a calendar table

e.g.

 

User nameCategory 1Category 2Created on
user 2approvednot approved5/1/23
user 1approvednot approved3/1/23
user 2 approvedapproved2/1/23
user 1not approvedApproved1/1/23
user 3not approvedapproved2/1/23

 

I would like to be able to display a bar chart with a date slider that can show for example the countof category one approved users on a chosen day...so if I filter to 4/1/23 then the count for category 1 approved user would be 2 (users 1and 2) and the count for category 1 not approved would be 1 (user3), count category 2 approved 2 (users 2 and 3), category 2 not approved 1 (user1) as it would only take into account the latest record for a user not the ones previously.  So data table filtered to 4/1/23 as below

UserNameCategory 1category 2latest Created on
user 2approvedapproved2/1/23
user 1approvednot approved3/1/23
user 3not approvedapproved2/1/23

 

and bar  chart as 

 

 

So far I have this measure w

Measure =
CALCULATE(FIRSTNONBLANK(new_partnerlandscapehistory[createdon],1),
FILTER(new_partnerlandscapehistory,new_partnerlandscapehistory[createdon] = MAX(new_partnerlandscapehistory[createdon])))
 
which manages to give me the last record up to that date in a table but can't get this to work with a count in barchart as then it counts the previous records also

 

 

 

thanks for your help 

 

Need Help
  • TheoC's avatar
    TheoC
    3 years ago

    Hi Mi_80 

     

    Firstly, make sure you have the relationship correctly linked between your Date table and fact table which should be Date[Date] and Table[Created On].  From here, you can try something like the following measure:

     

    Users (Approved) =

VAR _category = "approved"
Var _1 = 
CALCULATE (
    DISTINCTCOUNT ( Table[User name] ) ,
		FILTER ( ALL ( 'Table' ),
			Table[Category 1] = _category && Table[Created on] <= MAX ( 'Date'[Date] ) )
	)

RETURN

_1

     

    Make sure to update your Table and Column names.  Also, if you want to change the category, just create a new measure and update the _category from "approved" to whatever the other category is you're wanting.

     

    Hope this helps mate.

     

    Theo

     

12 Replies

  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Mi_80 

     

    Firstly, make sure you have the relationship correctly linked between your Date table and fact table which should be Date[Date] and Table[Created On].  From here, you can try something like the following measure:

     

    Users (Approved) =

VAR _category = "approved"
Var _1 = 
CALCULATE (
    DISTINCTCOUNT ( Table[User name] ) ,
		FILTER ( ALL ( 'Table' ),
			Table[Category 1] = _category && Table[Created on] <= MAX ( 'Date'[Date] ) )
	)

RETURN

_1

     

    Make sure to update your Table and Column names.  Also, if you want to change the category, just create a new measure and update the _category from "approved" to whatever the other category is you're wanting.

     

    Hope this helps mate.

     

    Theo

     

    • Mi_80's avatar
      Mi_80
      Icon for Helper I rankHelper I
      3 years ago

      You're a star.  That works fab thank you🤗

      • TheoC's avatar
        TheoC
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Mi_80 I am glad it worked! 

         

        If you could accept my code as the solution, that would be awesome! Once accepted, it allows other Community users to search the forum for the solution if they experience the same issue / challenge.

         

        Have a wonderful day and thanks again!

         

        Theo 🙂

         

    • Mi_80's avatar
      Mi_80
      Icon for Helper I rankHelper I
      3 years ago

      whoops, I thought this worked but it actually doesn;t it is double counting records still as in it filter to status approved and count distinct but doesn;t factor in that maybe after that fact an update was made that changed that status so the older approved status shouldn;t be counted.   Any suggestions?

      • TheoC's avatar
        TheoC
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Mi_80 measures definitely take into account any changes.  They are very dynamic in that regard.  Can you send me a screenshot of the output you are after versus the output you are getting?  More than happy to help!  Also, happy for you to connect on LinkedIn and we can set up a video chat. Whatever is easiest for you.

    • Mi_80's avatar
      Mi_80
      Icon for Helper I rankHelper I
      3 years ago

      Hi, any chance you could put the  measure formulas here, unable to download links thanks 🙂