Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mi_80
Helper I
Helper I

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 

 

Mi_80_0-1685376325642.png

 

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

 

Mi_80_1-1685376325631.png

 

 

thanks for your help 

 

1 ACCEPTED SOLUTION
TheoC
Super User
Super User

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

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

12 REPLIES 12
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

The link is working fine.  try again later.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks, how can I actually create a bar chart out of this?

Please try that on your own.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Sorry my vesrion of power bi is older so it won't let me open your file and I am not allowed to update on my system.  If you could send me some snapshots or the measures that would be very helpful...thanks

You should still be able to open the file (even if you have an older version).  Please try on another machine/installation.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
TheoC
Super User
Super User

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

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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?

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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

@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 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.