Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have the sample data like below
DW_ID DATE Head
1 05/30 3
1 05/29 4
1 05/28 6
2 05/24 11
3 05/30 14
3 05/22 10
In detail : I need the sum(Head) group by Dw_ID for the latest date of the DW_ID . how can i achieve this in DAx?
These dates will be applied from the filter slicer . If i select for example date :05/30 : Head =3+14 =17 . If i select the range of dates from 05/22 to 05/30 Head =14+11+3 =28 . How can i achieve this from Dax measure .?
Solved! Go to Solution.
Assuming you have a date table called DimDate which is related to your fact table. Using the dates from the DimDate table you can write the following measure:
Measure 2 = Var __LastDateTable = SUMMARIZE( Table2, Table2[DW_ID] , "LastDate", LASTDATE( Table2[Date]) ) Var __AddColumn = ADDCOLUMNS( __LastDateTable, "Value at Last Date", CALCULATE( SUM ( Table2[Head] ), FILTER( Table2, [LastDate] = Table2[Date] && Table2[DW_ID] = EARLIER( Table2[DW_ID]) ) ) ) RETURN CALCULATE( SUMX( __AddColumn, [Value at Last Date] ) )
Assuming you have a date table called DimDate which is related to your fact table. Using the dates from the DimDate table you can write the following measure:
Measure 2 = Var __LastDateTable = SUMMARIZE( Table2, Table2[DW_ID] , "LastDate", LASTDATE( Table2[Date]) ) Var __AddColumn = ADDCOLUMNS( __LastDateTable, "Value at Last Date", CALCULATE( SUM ( Table2[Head] ), FILTER( Table2, [LastDate] = Table2[Date] && Table2[DW_ID] = EARLIER( Table2[DW_ID]) ) ) ) RETURN CALCULATE( SUMX( __AddColumn, [Value at Last Date] ) )
@Anonymous : Can you help with difference one please below is description:
i have the simple scnerio as below .
dw_id diagname dead date
1 heartfailure 2 06/07/2019
2 kidney problem 3 04/07/2019
3 asthama 4 05/07/19
i have like 100 category of dignosisname and i have used the bar chart to show only 10 deads during that period . where i have the date as slicer and dw_id in the filterpane . i need to have the measure of dead % when i categorize by diganosisname = dead of that category/total dead . But when i strict the top 10 it is stricting the total count for that tile and % is showing the wrong value . For example i have total dead for the time period of jan 2019 is 400 and top 10 dead is limiting to 300 count . Value is being calculated on 300 instead of 400 . Is there any way i can ignore the top N filter for calculation ?
currently i am using measure like below :
dead % by diagnname = divide(sum(dead),calculate(sum(head),allexpect(date,dw_id))
can you upload some sample data?
@Anonymous
hi nick i have the data like below and i have the total dead of 36 with below records and i have the bar chart in below where i am showing the top 3 records in barchart and the barchart is showing the formula of top n with formula and PBX in below thread . Please refer below thread and want i am looking for in below thread . I hope you respond positively :
. My user is saying he need the formula in below way % for asthama should be = 11/36 =30.5 % but what you are showing is 40.74 .
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
78 | |
68 | |
55 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |