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 appro...
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

     

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

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.