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.
Hello,
I have two set of data as below.
My need to compare the Reference (Date, Hour, Minute) with Actual Source Data (Date, Hour, Minute) and returns the Value if Date, Hour, Minute is matched.
Basically, the operation will have to happen like below and go on:
I tried doing this with Loop and If Condition, but I couldn’t succeed. Can anyone please help or provide me a lead to make this happen!! Looking forward!! Thank you!!!
Solved! Go to Solution.
Hi @Anonymous
I get a result table as below ( filter [Hour] <=5 and [Date] =2019/1/1, so that it displays more clearly)
If it meets your requirement, please refer to the steps below
1. In Edit queries, in "Date" table, Add two custom column
Custom=#"Hour"
Custom.1=#"Minute"
then expand two custom columns
2.
In "Date" table,
select "Date", 'Custom.Hour", "Custom.1.Minute" , then "Add column"->Merge column(Separator is "-" in my example)
Then in "Actual Source Table",
select "Date", 'Hour", "Minute" , then "Add column"->Merge column(Separator is "-" too)
3.
In "Date" table, merge queries, then expand "Merged 2" column from "Source table"
4. Close&&apply, create a cacluated column
count = CALCULATE(COUNTROWS('Date'),FILTER(ALLEXCEPT('Date','Date'[Date]),[Actual Source Table.Merged2]<>BLANK()))
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Hi @Anonymous
I get a result table as below ( filter [Hour] <=5 and [Date] =2019/1/1, so that it displays more clearly)
If it meets your requirement, please refer to the steps below
1. In Edit queries, in "Date" table, Add two custom column
Custom=#"Hour"
Custom.1=#"Minute"
then expand two custom columns
2.
In "Date" table,
select "Date", 'Custom.Hour", "Custom.1.Minute" , then "Add column"->Merge column(Separator is "-" in my example)
Then in "Actual Source Table",
select "Date", 'Hour", "Minute" , then "Add column"->Merge column(Separator is "-" too)
3.
In "Date" table, merge queries, then expand "Merged 2" column from "Source table"
4. Close&&apply, create a cacluated column
count = CALCULATE(COUNTROWS('Date'),FILTER(ALLEXCEPT('Date','Date'[Date]),[Actual Source Table.Merged2]<>BLANK()))
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the function LOOKUPVALUE, that should be all you need. I do not see a need for looping here.
Thanks for the HeadsUp!!!
Actually I need to compare the Date, Hour & Minute and get the Value and count as well. Like how much value for Date 1/1/2019, 1/2/2019 and so on. It doesn't end up with 3 Dates. It may keep going for a year!! That's why i thought of Loop and If condition.
But I had an idea like below:
Output = Count of (Lookup( Reference Value, Reference Date = Source Date, Reference Hour = Source Hour, Reference Minute = Source Minute, "0", "1"))
Please let me know if the above concept works!! First it will look up the Date,Hour,Minute and then return a Value.
That Value will be counted as I am using Count function.
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.