Creating a measure that is a count of resolved cases and.From what I can tell, the only way I can do this in Power View is by: Say that I want a pie chart that shows what has been solved by "Jim' Vs everything else. The problem is that Power View is very limited in matters of what it can do with charts. You are right I could simply use a measure that counts the cases solved by Jim. It does makes me wonder then as to what would be the value of inactive relationships in a tabular model? What can be done with USERELATIONSHIP that could not be used with LOOKUPVALUE?Īs for the column that says whether a case has been solved by Jim or not, the reason behind such modelling choice is Power View. I can see how Charlie did it and it does make sense! (Thanks for that Charlie!) Thanks a lot for the clarification on the lookupvalue bit. This example is not readily achievable with LOOKUPVALUE(). This isĪ contrived example, but should show where USERELATIONSHIP() is useful. , USERELATIONSHIP( factTable, dimSupportRep )Īssuming the active relationship is from factTable this pair of measures will allow you to pull on your from dimSupportRep to the table, and then will indicate for each rep the total number of calls they touched. USERELATIONSHIP() makes aggregates much easier than LOOKUPVALUE().Įxample: Count of cases where rep X has had anything to do with it (Opened, Breached, or Closed): CaseCount:= If you have a business need where you only need the column for Jim, then I see no problem with it (though I can't imagine a viable example where this is the case - either way I don't need to know this). I understand why you want as a column, but the question I have is if you have a dozen reps, do you want to build a dozen columns? Will you have to alter the model and add a column every time you hire a rep? In this case it is not a viable What do you guys reckon? Any advice around those points? However for obvious reasons I would not use this column as an input for the 'Solve by Jim' column. Rep named 'Jim', this will certainly cause issues with aggregations and other types of groupings. Shall I make multiple copies of the dimSupportRep table for each relationship, or shall I create all of my DAX formulas involving the dimSupportRep simply by specifying the relationship to use?Īnother bit I am not sure about is on whether I shall also bring the rep names for each of the columns that points to a rep (OpenBy, BreachedBy, Solvedby) as calculated columns to the fact. Now I rather new to DAX and I can see different ways to skin this cat so I was wondering if I could get some advice from the community. * I need a TRUE/FALSE column named 'Solved by Jim' that is set to yes if SolvedBy points to a rep named Jim. Now I need to create some calculated columns and measures that contain DAX logic around the value of some of those fields. * SolvedBy: The support rep that solved the case. * BreachedBy: The support rep that owned the case when its SLA was breached. * OpenedBy: The support representative that opened the case. These are some examples of columns in the factTable that contains a foreign key pointing to the dimSupportRep table. This table has multiple relationships to a dimension table named dimSupportRep. This model will be used mainly to produce Powerview reports.īasically, consider a fact table containing data about support incidents. I'd like to ask the community for some advice and best practices around a very common scenario I have with an SSAS Tabular model.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |