Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have some data similar to the below:
date | user id | record id | record type | includes attachment? |
01/01/20 | A | 100 | X | Y |
01/01/20 | B | 101 | Y | N |
02/01/20 | A | 101 | X | Y |
02/01/20 | B | 103 | X | Y |
02/01/20 | C | 104 | Y | N |
03/01/20 | A | 105 | Z | N |
03/01/20 | B | 106 | X | N |
03/01/20 | C | 107 | Y | N |
I just want to calculate the percentage of users that always, never or sometimes included an attachment with their records.
The part giving me a headache is was hoping to calculate this dynamically with or without a specific record type selected and also for a filtered date range. Eg. the categorisation (sometimes, always, never) should be recalculated if filtering by a specific record type for a specific date range.
I had started by, in powerquery, grouping by user id and record type and then pivoting on 'record includes attachment'. While this lets me write a simple cound distinct user ids measure it is only accurate when a record type is selected and won't allow filtering by date.
I think I want to write a measure(s) that:
Can anyone suggest an approach to tackling this problem please? Any guidance much appreciated.
Solved! Go to Solution.
@Anonymous
I think I was able to get something close to what you are lookin for. The categorization of users and user count by categorization will both follow the slicer selections.
Categorization =
VAR _Y =
CALCULATE(
SUMX(
DISTINCT('Table'[user id]),1)
,'Table'[includes attachment?] = "Y"
)
VAR _N =
CALCULATE(
SUMX(
DISTINCT('Table'[user id]),1)
,'Table'[includes attachment?] = "N"
)
RETURN
SWITCH(
TRUE(),
_Y = 1 && _N = 0, "Always",
_Y = 1 && _N = 1, "Sometimes",
_Y = 0 && _N = 1, "Never"
)
User Count =
CALCULATE(
DISTINCTCOUNT('Table'[user id]),
FILTER(
VALUES('Table'[user id]),
COUNTROWS(
FILTER(
Categories, [Attach Yes] = Categories[Y] && [Attach No] = Categories[N])
) > 0
)
)
I did add a categories table for use when grouping the user counts.
I have attached my sample file for you to look at.
@Anonymous
I think I was able to get something close to what you are lookin for. The categorization of users and user count by categorization will both follow the slicer selections.
Categorization =
VAR _Y =
CALCULATE(
SUMX(
DISTINCT('Table'[user id]),1)
,'Table'[includes attachment?] = "Y"
)
VAR _N =
CALCULATE(
SUMX(
DISTINCT('Table'[user id]),1)
,'Table'[includes attachment?] = "N"
)
RETURN
SWITCH(
TRUE(),
_Y = 1 && _N = 0, "Always",
_Y = 1 && _N = 1, "Sometimes",
_Y = 0 && _N = 1, "Never"
)
User Count =
CALCULATE(
DISTINCTCOUNT('Table'[user id]),
FILTER(
VALUES('Table'[user id]),
COUNTROWS(
FILTER(
Categories, [Attach Yes] = Categories[Y] && [Attach No] = Categories[N])
) > 0
)
)
I did add a categories table for use when grouping the user counts.
I have attached my sample file for you to look at.
@jdbuchanan71 this is perfect, thanks very much!
Just what I needed and learnt a lot from the way you've laid out the various components. Really appreciate it.
@Anonymous , You can create a new column in the table like
category =
var _A = countx(filter(table,table[user id] = earlier([user id])),[user Id])
var _Y = countx(filter(table,table[user id] = earlier([user id]) && Table[includes attachment] ="Y"),[user Id])
var _N = countx(filter(table,table[user id] = earlier([user id]) && Table[includes attachment] ="N"),[user Id])
return
switch ( True(),
[_A] = [_Y] ,"Always",
[_A] = [_N] , "Never",
"Sometime")
You can also have measure like these
always =
countx(filter(summarize(table,table[user id], "_1", countrows(Table), "_2",calculate(countrows(Table),Table[includes attachment] ="Y")),[_1]=[_2]),[User id])
Never =
countx(filter(summarize(table,table[user id], "_1", countrows(Table), "_2",calculate(countrows(Table),Table[includes attachment] ="N")),[_1]=[_2]),[User id])
sometime =
countx(filter(summarize(table,table[user id], "_1", countrows(Table), "_2",calculate(countrows(Table),Table[includes attachment] ="Y")),not(isblank([_2])) && [_1]>[_2]),[User id])
Thanks very much for this, much appreciated. Definitely need to get my head around using earlier and also using switch. I was getting some slightly erroneous results when filtering on the record type but probably something on my end.
Thanks again
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |