LODS on Power Bi

[dbehara]

Hi Mate,

Im totally new to Power BI and i'm try to switch from Tableau to Power BI.
I have few LODS calculations created on Tableau and try to get those things on Power Bi but facing challenges, could anyone please help me with solutions on Power BI

Tableau Calculations
1. Points/day = If {Include [id], datetrunc('day',[Date]) : countd[case_id]} >10 then 1 else 0 end
2. Task_created = if {Include [id],datetrunc('day',[Date]): count[taskid] } >= 16 then 3 elseif {Include [id], datetrunc('day',[Date]) : count[taskid] } >=6 then 2 else 1 end

basically im creating a point system for individual [id] upon the date value. for particular day if [case_id] greater than 10 we need to give 1point same way for [taskid].

datetrunc is used to get particular day, as [Date] coulmn is datetime field

Sample Data

id	Date	Case_id	taskid	Points/day	Task_created
1	07/22/2021 11:45	null	DB20A8	0	1
1	07/22/2021 11:45	null	SN06O3
1	07/22/2021 11:46	DB968R	HJ367F
1	07/22/2021 11:46	UD3771	EJ650)
1	07/22/2021 11:46	KI772^	SH925;
1	07/23/2021 08:48	null	null	0	0
1	07/23/2021 08:48	EB2880	BD961Z
1	07/24/2021 11:45	YB139Z	GQ968R	0	0
1	07/24/2021 11:51	VH143X	EA110]
1	07/25/2021 07:23	CZ345b	IF7244	0	2
1	07/25/2021 09:49	LJ568	HC407@
1	07/25/2021 09:49	QC105Q	QO709
1	07/25/2021 09:49	NL970C	PA901"
1	07/25/2021 09:49	FQ653	ZA198*
1	07/25/2021 09:49	JE666	YK767
1	07/25/2021 09:49	NA292	UP858
1	07/25/2021 11:49	PV218[	YB548(
1	07/28/2021 11:38	null	Null	1	3
1	07/28/2021 11:38	YX734S	NX920
1	07/28/2021 11:39	MV373	EJ650)
1	07/28/2021 11:40	JU9328	DK944[
1	07/28/2021 11:41	PG596	YK836!
1	07/28/2021 11:42	SJ274	BU745X
1	07/28/2021 11:43	null	JP712
1	07/28/2021 11:44	GE398	TA131
1	07/28/2021 11:45	ZY976a	HO229,
1	07/28/2021 11:47	WT426	WE560
1	07/28/2021 11:47	AB330I	XD918b
1	07/28/2021 11:50	UD3771	GH728G
1	07/28/2021 11:50	YK222S	TF843
1	07/28/2021 11:51	XI686R	MH647S
1	07/28/2021 11:52	OG940a	WL525R
1	07/28/2021 11:52	QF7774	JT795?
1	07/28/2021 11:53	VE494	XS255
1	07/28/2021 11:53	AZ876W	PP263a
2	07/28/2021 11:53	OG940a	CR426D	1	3
2	07/28/2021 11:53	FG732$	OO382-
2	07/28/2021 11:54	AZ876W	SR146;
2	07/28/2021 11:54	NX956M	JL114E
2	07/28/2021 11:39	MF197C	SP2357
2	07/28/2021 11:39	RL5280	RY680U
2	07/28/2021 11:39	DB454c	Null
2	07/28/2021 11:39	HE7578	ZF522"
2	07/28/2021 11:52	RC801	LD897W
2	07/28/2021 11:52	VV329	VQ461S
2	07/28/2021 11:51	VV3011	WF808
2	07/28/2021 11:51	PB374	YB3719
2	07/28/2021 11:48	QF779a	LB543\
2	07/28/2021 11:48	AJ196A	RE390
2	07/28/2021 11:49	IY400	KW111J
2	07/28/2021 11:49	QU211a	ZP727
2	07/28/2021 11:50	CI134	SR146;
2	07/28/2021 11:50	DT560	MB542

logic used

1. points/day = if distinct case_id =>10 for any particular day then should get 1 point (no limit anything above 10)

2.Task_created = if taskid count => 5 then 1, elseif count=>6 and count <= 15 then 2, elseif count=>16 then 3 

Thanks for help 🙂 really appreciate 

[v-yiruan-msft · Accepted solution]

Hi @dbehara ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

First, please add custom column to get the date part of Date column in Power Query Editor and add Index column group by id and new custom column with date:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZVZc9owEMe/Ssv0oe3QiXZ1rEQeWoMxYC6DuSGdSdLmKdO3fv9qBSVuYrlPXsk/HXv9dTy2oNVu0Q3iDQqEdwAdpf3Mr9/Pz/6TdlEktnXX/g9XzoSZywhnwkbO2KU3hrk0lDWQ61QS8a9+brT41ECOR0T4nU8fOtS3VVKeSdtR9uWS4ROF+l20VnijmzoDhyqo/vV53wXpDt4YLIJTtaTmmc0QlNzx5gmAuKuS+kxSB6Wf6B2k0g/eGGWEStWArqOcn5jk2tjTEweypwR9i5OLHgi9YGNOwp1+CyF0nJ5NHImeN4rECTi14mS2MFqG7ciPDgk4+zlO531jDNOgOHJjMhTWNrg4S9BhWHLP5VBYbcPoZ80STgmvKTYI9hhyo5X9WCXtlZSVWpi9qoVX1H5HUpXM7RyKcGMZwfn46UbSOSZPtYX7giuusHztJPIx6dgpdYyQPFMMtDuHD0P4rDTvIzgDZY6kAh4KeU1K7yK4fIlFXhDg5fb1MOdu0JfOXiLRbq0SkHCpgvo13CmHvSPDWRzOEV07QnIdbVcKz45yF2z72ogGOulKKUbe2KUO7EM9qUVVTQZDQjtoIPdjROSUrzKrQjIh4lpo7d3IWMOCNh0aRWWE5KTMB04JDsJ2olEvG8hFRkQc63xFTn+NkBz+TV+5c6If+S4lat1UpGFNcrBktlxUBRp5H0isJa837i19VtIGMhuQxA+8ZC4tfomQqnp6uQRlbhtI33LaTDkKEwDVryfPbZeBIxatskCpqYFc+tAHcV/ujRXrBjLtKq0eqwpRzw37pIl7+JBpxItgvnWIZ5Y96yWVlWGSWkfbBnSzkRi0Gn7waKEMlBEczrgANraZFbYBLLryIgxPQSQlgavHw1vIhei4ACZeTuXp1IAmOTiTsJdeH4JSRnfmsI32Sohr4Y63AJA34Is1QngGDgUhVZryrZuc3d4IpLq+No2Vxni68jIT+ob/T72v+PedvPsD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, Date = _t, Case_id = _t, taskid = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"Date", type datetime}, {"Case_id", type text}, {"taskid", type text}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "NDate", each DateTime.Date([Date]), type date),
    #"Grouped Rows" = Table.Group(#"Inserted Date", {"id", "NDate"},  {{"Index", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Date", "Case_id", "taskid", "Index"}, {"Date", "Case_id", "taskid", "Index"})
in
    #"Expanded Index"

Then create the below calculated columns to get Points/day and Task_created flag:

Points/day = 
VAR _countofcases =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Case_id] ),
        ALLEXCEPT ( 'Table', 'Table'[id], 'Table'[NDate] )
    )
VAR _minindex =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        ALLEXCEPT ( 'Table', 'Table'[id], 'Table'[NDate] )
    )
RETURN
    IF ( 'Table'[Index] = _minindex, IF ( _countofcases > 10, 1, 0 ), BLANK () )

Task_created = 
VAR _countoftasks =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[taskid] ),
        ALLEXCEPT ( 'Table', 'Table'[id], 'Table'[NDate] )
    )
VAR _minindex =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        ALLEXCEPT ( 'Table', 'Table'[id], 'Table'[NDate] )
    )
RETURN
    IF (
        'Table'[Index] = _minindex,
        IF ( _countoftasks >= 16, 3, IF ( _countoftasks >= 6, 2, 1 ) ),
        BLANK ()
    )

Best Regards

[Greg_Deckler]

@dbehara This will be easier if you post sample data and expected output. Also, if you are trying to do this in DAX or Power Query (M). If statements in DAX are the same as in Excel, IF(<condition>,<if true return this>, <if false return this>). Not sure what datetrunc is doing or why you need it. You can use COUNT or COUNTROWS for counting. Again, it's hard to interpret what you are doing or the context of how you are using these calculations without additional detail.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[dbehara]

@Greg_Deckler Hi Greg, sorry for the missing input.. i have modified the post hope it will help you. do let me know if im missing anything

Thanks for help