Forum Discussion
DAX query
I'm new to Dax and have a couple of related tables:
I want to get a list of all the trucks that are active from the first table and get the kms of the table from the second table for a given day and I want to show all the active trucks whether or not they have logged any kms. So is there a way to get this output using Dax:
Any help appreicated.
hello randomsky
yes, that happens because the idle truck doesnt have date, so it will be filtered up by your slicer.
if you have to do this no matter what, i would do something like this.
1. following what you have done, put your IsTruck and IsActive in your table. then lock IsTruck and IsActive TRUE value as visual filter.
2. if you dont want to show IsTruck and IsActive in your table, after being locked then remove from your column value.
There you have it.
Hope this will help.
Thank you.
3 Replies
hello randomsky
please check if this accomodate your need.
as your description, you want to get value for IsActive = YES and IsTruck = YES.
In your output, you want to get the minimum date for each ID (i.e ID 1 have two value 1/1/24 and 2/1/24 but the value shown in output table is 1/1/24).
1. create a new table with following DAX. This will summarize all value with IsActive = YES and IsTruck = YES
Summarize =
SUMMARIZE(
FILTER(
'Equipment',
'Equipment'[IsActive]="TRUE"&&
'Equipment'[IsTruck]="TRUE"
),
'Equipment'[ID],
'Equipment'[Name]
)2. create a calculated column with following DAX to extract minimum date of each ID.
ActivityDate =
MINX(
FILTER(
'Equipment KMs',
'Equipment KMs'[EquipmentID]='Summarize'[ID]
),
'Equipment KMs'[ActivityDate]
)3. create a calculated column with following DAX to extract KMs for specific ActivityDate and specific ID
KMs =
MINX(
FILTER(
'Equipment KMs',
'Equipment KMs'[ActivityDate]='Summarize'[ActivityDate]&&
'Equipment KMs'[EquipmentID]='Summarize'[ID]
),
'Equipment KMs'[Kms]
)4. create table visual to show your data.
Hope this will help.
Thank you.
Hi Irwan , thanks for your help. That's pretty close execpt that I don't want the MINX of the date. I want the date to be the filter so that when I add a slicer with the ActivityDate field, the user is able to select the date from a dropdown and it then shows the trucks for that date but it will also show the idle trucks. Currently when I do this it only shows the trucks that were active on that date but not the idle trucks i.e. FD4
hello randomsky
yes, that happens because the idle truck doesnt have date, so it will be filtered up by your slicer.
if you have to do this no matter what, i would do something like this.
1. following what you have done, put your IsTruck and IsActive in your table. then lock IsTruck and IsActive TRUE value as visual filter.
2. if you dont want to show IsTruck and IsActive in your table, after being locked then remove from your column value.
There you have it.
Hope this will help.
Thank you.
