Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 name | Category 1 | Category 2 | Created on |
user 2 | approved | not approved | 5/1/23 |
user 1 | approved | not approved | 3/1/23 |
user 2 | approved | approved | 2/1/23 |
user 1 | not approved | Approved | 1/1/23 |
user 3 | not approved | approved | 2/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
UserName | Category 1 | category 2 | latest Created on |
user 2 | approved | approved | 2/1/23 |
user 1 | approved | not approved | 3/1/23 |
user 3 | not approved | approved | 2/1/23 |
and bar chart as
So far I have this measure w
thanks for your help
Solved! Go to Solution.
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
Hi,
You may download my PBI file from here.
Hope this helps.
Hi, any chance you could put the measure formulas here, unable to download links thanks 🙂
The link is working fine. try again later.
Thanks, how can I actually create a bar chart out of this?
Please try that on your own.
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.
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |