Forum Discussion
SQL to DAX conversion
Try this New Table from Modelling Tab
New Table = SUMMARIZE ( C, C[Start Time], C[End Time], "Count", CALCULATE ( COUNT ( D[Operating value] ), FILTER ( D, D[Operating value] <= 5 && D[Process Time] >= C[Start Time] && D[Process Time] <= C[End Time] ) ) )Another way
New Table2 = CALCULATETABLE ( SUMMARIZE ( C, C[Start Time], C[End Time], "Count", CALCULATE ( COUNT ( D[Operating value] ), FILTER ( D, D[Process Time] >= C[Start Time] && D[Process Time] <= C[End Time] ) ) ), D[Operating value] <= 5 )
Hello Zubair_Muhammad,
I tried your query and it took very close to my solution.Only thing I am not getting in my output is Count of Operating Value.
All values in this column are NULL. I tried to debug, When I run subset of your code:
New Table =
SUMMARIZE (
C,
C[Start Time] ,
C[End Time],
"NoOfEvents", COUNT (D[OperatingValue] )
)
Still I am getting Null values for "NoOfEvents". I checked my data I also have data for it.
I am not able to debug further. Can you please suggest where my code is going wrong.
Thanks
I believe there in no relationship between C and D.
Thats why the subset code you wrote is retruning zeros
In my code i dad mimiced this relationship using Calculate and Filter
Zubair_MuhammadThanks for the reply!!
But when I use Calculate and Filter in my above code it still gives NULL value. I am not able to figure out where I am going wrong.
ThanksHello Zubair_Muhammad
I am getting NULL values for some start and End Time instead of having data.I am not able to figure out where I am going wrong.Please find the below file:
https://www.dropbox.com/s/mo49p1ohzb5dfgo/SampleFile.pbix?dl=0
Thanks in Advance!