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 a column that contains dates that cases were opened - 'Cases'[CreatedDate] with a date/time format of 12/26/2016 5:56:17 PM
In a second table I have all corporate holidays listed - 'Holidays'[Holiday Date] with a date format of 12/16/2016
I want to build a calculated column that identifies matching dates and produces a result of "True" in the column if the CreatedDate matches a Holiday Date.
I built a relationship between the values and was able to get a column to show the date, but can't figure out how to build the matching output I'm trying to get.
Solved! Go to Solution.
Hi @Anonymous
“I built a relationship between the values and was able to get a column to show the date”
Do you mean “built a relationship between the values” instead of building a relationship on the date column?
Does the following example do as you said?
Then with a DAX, I can lookup “Holiday” based on “value” as follows.
Next, identify matching dates and produce a result of "True"
Column = LOOKUPVALUE(Holidays[Holidays],'Cases'[CreatedDate],[CreatedDate])
flag = IF([Column]=[CreatedDate],"true",BLANK())
Best Regards
Maggie
Hi @Anonymous
“I built a relationship between the values and was able to get a column to show the date”
Do you mean “built a relationship between the values” instead of building a relationship on the date column?
Does the following example do as you said?
Then with a DAX, I can lookup “Holiday” based on “value” as follows.
Next, identify matching dates and produce a result of "True"
Column = LOOKUPVALUE(Holidays[Holidays],'Cases'[CreatedDate],[CreatedDate])
flag = IF([Column]=[CreatedDate],"true",BLANK())
Best Regards
Maggie
There is a lot to learn and do.
Remove the time portion from your column
create a calendar table. https://exceleratorbi.com.au/power-pivot-calendar-tables/
Join both current tables to be calendar table, then use the calendar table date column and not the other date columns https://exceleratorbi.com.au/relationships-power-bi-power-pivot/
dont Write a calculate column https://exceleratorbi.com.au/calculated-columns-vs-measures-dax/
depending on what you are trying to do, you could then creat a matrix using calendar date on rows, then pull in the count of cases and the holiday flag.
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |