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 BITableau Calculations1. Points/day = If {Include [id], datetrunc('day',[Date]) : countd[case_id]} >10 then 1 else 0 end2. 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 endbasically 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 DataidDateCase_idtaskidPoints/dayTask_created107/22/2021 11:45nullDB20A801107/22/2021 11:45nullSN06O3 107/22/2021 11:46DB968RHJ367F 107/22/2021 11:46UD3771EJ650) 107/22/2021 11:46KI772^SH925; 107/23/2021 08:48nullnull00107/23/2021 08:48EB2880BD961Z 107/24/2021 11:45YB139ZGQ968R00107/24/2021 11:51VH143XEA110] 107/25/2021 07:23CZ345bIF724402107/25/2021 09:49LJ568HC407@ 107/25/2021 09:49QC105QQO709 107/25/2021 09:49NL970CPA901" 107/25/2021 09:49FQ653ZA198* 107/25/2021 09:49JE666YK767 107/25/2021 09:49NA292UP858 107/25/2021 11:49PV218[YB548( 107/28/2021 11:38nullNull13107/28/2021 11:38YX734SNX920 107/28/2021 11:39MV373EJ650) 107/28/2021 11:40JU9328DK944[ 107/28/2021 11:41PG596YK836! 107/28/2021 11:42SJ274BU745X 107/28/2021 11:43nullJP712 107/28/2021 11:44GE398TA131 107/28/2021 11:45ZY976aHO229, 107/28/2021 11:47WT426WE560 107/28/2021 11:47AB330IXD918b 107/28/2021 11:50UD3771GH728G 107/28/2021 11:50YK222STF843 107/28/2021 11:51XI686RMH647S 107/28/2021 11:52OG940aWL525R 107/28/2021 11:52QF7774JT795? 107/28/2021 11:53VE494XS255 107/28/2021 11:53AZ876WPP263a 207/28/2021 11:53OG940aCR426D13207/28/2021 11:53FG732$OO382- 207/28/2021 11:54AZ876WSR146; 207/28/2021 11:54NX956MJL114E 207/28/2021 11:39MF197CSP2357 207/28/2021 11:39RL5280RY680U 207/28/2021 11:39DB454cNull 207/28/2021 11:39HE7578ZF522" 207/28/2021 11:52RC801LD897W 207/28/2021 11:52VV329VQ461S 207/28/2021 11:51VV3011WF808 207/28/2021 11:51PB374YB3719 207/28/2021 11:48QF779aLB543\ 207/28/2021 11:48AJ196ARE390 207/28/2021 11:49IY400KW111J 207/28/2021 11:49QU211aZP727 207/28/2021 11:50CI134SR146; 207/28/2021 11:50DT560MB542 logic used1. 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

Forum Discussion

Frequent Visitor

5 years ago

Solved

LODS on Power Bi

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

Need Help

Anonymous

5 years ago

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

LODS on Power Bi.pbix56 KB

3 Replies

Anonymous

Not applicable

5 years ago

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

LODS on Power Bi.pbix56 KB

Greg_Deckler
Community Champion
5 years ago
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
  Frequent Visitor
  5 years ago
  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

Forum Discussion

LODS on Power Bi

3 Replies

Related content

LOD Calculations

LOD? DAX?

¿LOD? ¿DAX?

LOD (tableau) into Power BI?

Power BI - tableau LODs