Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a new problem.
My data returns all of the trouble reports for a product, they have all the key information in this dataset in order to put them into trouble types and have those dated
I have the following columns for the needed information.
Create_Time - in MM/DD/YYYY format
Trouble Description - text
TKT_ID - Text
I can use the Table to get a Count based on filters of date ( Month, Year, Week)
I have a calculated field that will categorized them based off of a table I created and imported with keywords, category and an ID field for numerical ordering.
The formula for that is a 2 part process
Part 1
Main_KeywordMatch =
LOOKUPVALUE(
'Main_KW'[Main_KW],'Main_KW'[ID],
CALCULATE(
LASTNONBLANK('Main_KW'[ID],1),
filter(
VALUES(Main_KW[Main_KW]),
SEARCH('Main_KW'[Main_KW],'Main_Table'[TRBL_DESC],1,0)<>0
)
))
Part 2
MainCategory =
LOOKUPVALUE(
Main_KW[Main_Cat],Main_KW[Main_KW],Main_Table[Main_KeywordMatch]
)
This is how I display a count of the tickets which can be filtered by the Month,Day,Year calculated fields on the PowerBI page.
I want now to take the Count Value for the calculated field of Categories and divide that by the count of Days Filtered that the user selects. I am running into issues and its returning an answer of "Infinity"
I am wondering how to achieve this. If I havent provided enough information please let me know and I will gather what ever is missing asap.
Solved! Go to Solution.
@CrownWorker,
If you want to drag the measure to the existing Matrix visual you create, you can create the measure using DAX below, the measure value will be evaluated by each main category in your visual, and the measure value will be changed when you use slicers to filter the Matrix,.
Measure = CALCULATE(Count(Main_Table[MainCategory]))/[DateDiff]
If the above step still doesn't help, please post a screenshot about your expected result.
Regards,
Lydia
Hey,
it would be really helpful if you could prepare some sample data, upload a pbix to onedrive or dropbox and share the link.
It's difficult (at least to me) to follow your explanations about your requirements w/o the possibility to look at some data.
Regards
Tom
Here is a link to a pbix that has all the customer information stripped out.
What I am trying to achieve is 2 things.
1) a static (this should be the easier of the two)
daily average of tickets for each of my main categories
weekly average of tickets for each of my main categories
Yearly average of tickets for each of my main categories
2) a dynamic (user filtered)
daily average of tickets for each of my main categories
weekly average of tickets for each of my main categories
Yearly average of tickets for each of my main categories
File location:
https://1drv.ms/u/s!AruhgvkyQfsfk3vjJCI4HdCnGSAN
let me know if this doesnt clear it up or if the data requires more information than is in the tables.
@CrownWorker,
Right click your table, and choose "New Measure" , then apply the following dax formula. And you can filter the measure using your slicers.
Measure = CALCULATE(Count(Main_Table[MainCategory]),ALLSELECTED(Main_Table))/[DateDiff]
Regards,
Lydia
Hello Lydia,
That's not exactly the solution I am looking for but it did give me a number I can use. That will give me the total of all of my tickets during the selected period of time as a measure. I am looking to get the same thing for each individual category though as a column.
Thank you for the reply and work you put into this though.
I am looking to get the same thing for each individual category though as a column.
@CrownWorker,
Could you please explain more details about the above requirement? What output would like to display? Do you only want to create a calculated column?
Regards,
Measure = CALCULATE(Count(Main_Table[MainCategory]),ALLSELECTED(Main_Table))/[DateDiff]
This calculation in a format that wouls work for a column so that I can use it in a "Matrix" and when I filter by Date/Other filters all of the individual categories will update at once in the table. Right now if I try to use this it returns a value of "Infinity".
@CrownWorker,
If you want to drag the measure to the existing Matrix visual you create, you can create the measure using DAX below, the measure value will be evaluated by each main category in your visual, and the measure value will be changed when you use slicers to filter the Matrix,.
Measure = CALCULATE(Count(Main_Table[MainCategory]))/[DateDiff]
If the above step still doesn't help, please post a screenshot about your expected result.
Regards,
Lydia
That worked perfectly, thank you very much and sorry for the late reply.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |