Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello Community.
I am trying to retrieve the TagId at Max EventRaised for each MachineID in a Direct Query.
From the picture below.
31207 = Press.31207.Störning1Utast1
Kit_Pack1 = KITLinan.Pack1.Automat
Thank you for any assistance!
Solved! Go to Solution.
share your pbix.
and where are you adding this query
select A.* from dbo.events a
left join (select MachineID,max(EventRaised) _EVENT from dbo.[events]
Group by MachineID) b ON A.MachineID=B.MachineID AND a.EventRaised=B._EVENT
WHERE b._EVENT IS NOT NULL
Get Data->Select Sql server -> ENter server name & database name->DirectQuery Mode-> Click on advance -> paste this query there ->
And load your data.
this will give you direct result set
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
Hi @lukasjar ,
Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help other members in community find it easily if they face the same problem with you. Thank you.
Best Regards
Rena
We have this function in latest release - lastnonblankvalue . I have tested for this case. But try like
https://docs.microsoft.com/en-us/dax/lastnonblankvalue-function-dax
calculate(max(table[tag_id]),filter(table,table[event_raised] = lastnonblankvalue(machine_id,event_raised)))
I received a value for 3/11 MachineId. Don't understnad why it wouldnt return the other ones.
measure is automatically filtering for those rows only which are in filter context with non blank values.
Got this error now.
A single value for column 'TagId' in table 'Events' cannot be determined....
I do not have any filters active in my filter fields.
Hi @lukasjar
you have not closed bracket after tagid in return section.
Calculate(max(table[tagid]),filter())
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
That solved it not giving an error, but still dosnt return anything =(
Yes that screenshot is from the code written by @amitchandak pasted below.
Now this code returns on 6 of the 11 MachineId I have. I do not understand why it dosnt send anything on the other ones as it has a TagId. The measure you pasted does not work for me. Can it have something to do with my date having date + timestamp? I am looking for the last timestamp. In query editor I have filtered down the data to just include the last day.
hi @lukasjar
Please check belox dax.
Unfortunatly still not working.
The first two are working on a imported query, not on direct query.
The third solution you suggested I get an error message, pic below.
Hi @lukasjar
I have created your datset on sql server and created directQUery.
The above two measure are showing same result which i was getting using import.
DO one thing create new pbix file and check.
Measures are working fine with directquery and yah i need to look onto column issue.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
I do not understand why it is not working on my end then, but must be something wrong in what I am doing i guess.
I have done a new pbix file with a new query as you wrote in the second message. Still have the same result. Will try sending you a movie of the pbix and measurments.
select A.* from dbo.events a
left join (select MachineID,max(EventRaised) _EVENT from dbo.[events]
Group by MachineID) b ON A.MachineID=B.MachineID AND a.EventRaised=B._EVENT
WHERE b._EVENT IS NOT NULL
share your pbix.
and where are you adding this query
select A.* from dbo.events a
left join (select MachineID,max(EventRaised) _EVENT from dbo.[events]
Group by MachineID) b ON A.MachineID=B.MachineID AND a.EventRaised=B._EVENT
WHERE b._EVENT IS NOT NULL
Get Data->Select Sql server -> ENter server name & database name->DirectQuery Mode-> Click on advance -> paste this query there ->
And load your data.
this will give you direct result set
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
hI @lukasjar
Use below query while connecting to your datasource using directquery.
select A.* from dbo.events a
left join (select MachineID,max(EventRaised) _EVENT from dbo.[events]
Group by MachineID) b ON A.MachineID=B.MachineID AND a.EventRaised=B._EVENT
WHERE b._EVENT IS NOT NULL
I have given you some measures in last post if you want your output using measures.
There are some limitation while creating Calculated column when source is directquery.
Hence calculate,Allexcept,filter and earlier are not working in previous formula.
Check it out here
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
Hi @lukasjar ,
Please try the below measure:
TagIdN = var a=max('Events'[MachineId]) var b=CALCULATE(max('Events'[EventRaised]),ALLEXCEPT('Events',Events[MachineId]))
var c= CALCULATE(max('Events'[TagId]),FILTER('Events','Events'[MachineId]=a&&'Events'[EventRaised]=b)) return c
Best Regards
Rena
You can use a subquery. The subquery will get the Max(CompletedDate). You then take this value and join on your table again to retrieve the note associate with that date:
select ET1.TrainingID,
ET1.CompletedDate,
ET1.Notes
from HR_EmployeeTrainings ET1
inner join
(
select Max(CompletedDate) CompletedDate, TrainingID
from HR_EmployeeTrainings
--where AvantiRecID IS NULL OR AvantiRecID = @avantiRecID
group by TrainingID
) ET2
on ET1.TrainingID = ET2.TrainingID
and ET1.CompletedDate = ET2.CompletedDate
where ET1.AvantiRecID IS NULL OR ET1.AvantiRecID = @avantiRecID
Hi @lukasjar
Try this measure
Measure =
VAR __maxEventRaised =
CALCULATE(
MAX( 'Table'[EventRaised] ),
ALLEXCEPT( 'Table', 'Table'[MachineId] )
)
RETURN
CALCULATE(
SELECTEDVALUE( 'Table'[TagId] ),
'Table'[EventRaised] = __maxEventRaised
)
Hi Mariusz.
It returns blank.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
105 | |
78 | |
35 | |
35 |
User | Count |
---|---|
157 | |
103 | |
71 | |
65 | |
53 |