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 7 tables , each tables has various rows and columns. One column is Dates ( in each table ).
I want to create a calendar using DAX that picks up the MAX from these tables columns.
The min value is picked up from another table.
any suggestion ?
Solved! Go to Solution.
Hi @Kaveh
Please correct me if I wrongly understood your issue .
You need to return the Max date from these multiple tables and then return the MIX date from these multiple tables .Then use the two dates to create a new Calendar date table .
Create a table with the following formula .
Calendar Date =
var _MIN=MINX({MIN('Table 1'[Date 1]),MIN('Table 2'[Date 2]),MIN('Table 3'[Date 3]),MIN('Table 4'[Date 4])},[Value])
var _MAX=MAXX({MAX('Table 1'[Date 1]),MAX('Table 2'[Date 2]),MAX('Table 3'[Date 3]),MAX('Table 4'[Date 4])},[Value])
return CALENDAR(_MIN,_MAX)
And the final result is as shown(Max date is 2021-04-05 and Min date is 2021-01-01) :
[value] is used to return some values in the previous {}, Take the above formula as an example: {MIN('Table 1'[Date 1]),MIN('Table 2'[Date 2]),MIN('Table 3'[Date 3]),MIN('Table 4'[Date 4])},[Value] , here [value] is used to return the min date in the four tables, and the final result is 2021-01-01,2021-02-01,2021-03-01,2021-04-01 . Is this explanation clear ?
I have attached my pbix file ,you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Kaveh
Please correct me if I wrongly understood your issue .
You need to return the Max date from these multiple tables and then return the MIX date from these multiple tables .Then use the two dates to create a new Calendar date table .
Create a table with the following formula .
Calendar Date =
var _MIN=MINX({MIN('Table 1'[Date 1]),MIN('Table 2'[Date 2]),MIN('Table 3'[Date 3]),MIN('Table 4'[Date 4])},[Value])
var _MAX=MAXX({MAX('Table 1'[Date 1]),MAX('Table 2'[Date 2]),MAX('Table 3'[Date 3]),MAX('Table 4'[Date 4])},[Value])
return CALENDAR(_MIN,_MAX)
And the final result is as shown(Max date is 2021-04-05 and Min date is 2021-01-01) :
[value] is used to return some values in the previous {}, Take the above formula as an example: {MIN('Table 1'[Date 1]),MIN('Table 2'[Date 2]),MIN('Table 3'[Date 3]),MIN('Table 4'[Date 4])},[Value] , here [value] is used to return the min date in the four tables, and the final result is 2021-01-01,2021-02-01,2021-03-01,2021-04-01 . Is this explanation clear ?
I have attached my pbix file ,you can refer to it .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
what does [value] refer to ?
You can try an expression like this to do that.
Calendar =
VAR vMinDate =
MIN ( Table1[Date1] )
VAR vMaxDate =
MAXX (
{ MAX ( Table2[Date2] ), MAX ( Table3[Date3] ), MAX ( Table4[Date4] ) },
//add more as needed
[Value]
)
RETURN
CALENDAR ( vMinDate, vMaxDate )
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |