Forum Discussion
new user question
New, as in today. I'm trying to teach myself this, and for my first project I've brought in 2 tables and related them correctly. One is a table of attempts with user and location, and the other keeps track of reasons for failure. I want a measure that has success percentage like 1-(count(failures)/count(attempts)) and be able to drop the location or user on the chart. So, do I build measures for the 2 counts, and then do the math in another measure? In which table should the measures reside? I'm not getting the concepts yet. Thanks in advance.
Hi BillR,
>>So, do I build measures for the 2 counts, and then do the math in another measure?
You can direct count them in the same formula.(measures will affect the performance)
>>In which table should the measures reside?
For my opinion, I will to put the measure at the side of part calculation
In addition, you can also take a look to below sample if it suitable for your requirement:
1. Tables.
attempts: GUID, UserID, Location (guid is unique id of each records)
failure records: GUID, Reasons (guid is from attempts table)
2. Relationship: GUID to GUID "one to many" relationship with "both" cross filter direction.
3. Measure formulas:
Percent = DIVIDE(COUNT('failure records'[GUID]),COUNT(attempts[GUID]))
Success Rate = 1-[Percent]4. Create table visual.
Regards,
Xiaoxin Sheng
2 Replies
I would certainly recommend doing it the way you have suggested. From a programming background i'm a big believer in doing things in modular ways, especially when you might want to use those results in other places.
Something you will eventually trip over, there are some Dax expressions that won't accept measures as a parameter. In those occasions you have to take your measure's code, and place it within that expression instead.
Hi BillR,
>>So, do I build measures for the 2 counts, and then do the math in another measure?
You can direct count them in the same formula.(measures will affect the performance)
>>In which table should the measures reside?
For my opinion, I will to put the measure at the side of part calculation
In addition, you can also take a look to below sample if it suitable for your requirement:
1. Tables.
attempts: GUID, UserID, Location (guid is unique id of each records)
failure records: GUID, Reasons (guid is from attempts table)
2. Relationship: GUID to GUID "one to many" relationship with "both" cross filter direction.
3. Measure formulas:
Percent = DIVIDE(COUNT('failure records'[GUID]),COUNT(attempts[GUID]))
Success Rate = 1-[Percent]4. Create table visual.
Regards,
Xiaoxin Sheng