March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello,
I have a data that looks like this:
Opportunity | Project | Project Name | Category Bk | Date |
042452503602QVayAAG | PROJ-19191 | Zoora Bank- Support on Telco | RC_CONSULTING_TIME_AND_MATERIALS | 11/07/2023 |
RC_CONSULTING_TIME_AND_MATERIALS | 06/07/2023 | |||
RC_CONSULTING_TIME_AND_MATERIALS | 05/07/2023 | |||
RC_CONSULTING_TIME_AND_MATERIALS | 27/06/2023 | |||
RC_CONSULTING_TIME_AND_MATERIALS | 22/06/2023 | |||
RC_PS_TM | 05/01/2023 | |||
RC_PS_TM | 04/01/2023 | |||
RC_PS_TM | 03/01/2023 | |||
RC_PS_TM | 22/12/2022 | |||
RC_PS_TM | 21/12/2022 | |||
RC_PS_TM | 20/12/2022 | |||
RC_PS_TM | 19/12/2022 |
My objective is to show the 'Current Category BK' based on the date selected (like parameters in Tableau in showing the values off a date parameter)
I wanted to create a table, that will generate outut like this below:
Opportunity | Project | Project Name | Current Category BK |
042452503602QVayAAG | PROJ-19191 | Zoora Bank- Support on Telco | RC_PS_TM |
Let's just assume that the Selected Date Parameter Dropdown:
05/01/2023 |
That is why the value for Current Category BK is 'RC_PS_TM'
In Power BI, what would be the formula for the Current Category BK?
I have already created a Date Parameter:
Solved! Go to Solution.
Hi @cruzp ,
(1) As @Ashish_Mathur said, click "transform data" to enter the power query editor, in the tab [transform] to find "replace values" in the empty replace null, and then click "fill down", the first three columns to fill, and then click "close and apply ".
(2)Create tables.
Calendar = CALENDARAUTO(12)
New Table = 'Table'
(3)Create measures.
SelectedDateValue = SELECTEDVALUE('Calendar'[Date])
Flag = IF(MAX('New Table'[Date])=[SelectedDateValue],1,0)
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @cruzp ,
(1) As @Ashish_Mathur said, click "transform data" to enter the power query editor, in the tab [transform] to find "replace values" in the empty replace null, and then click "fill down", the first three columns to fill, and then click "close and apply ".
(2)Create tables.
Calendar = CALENDARAUTO(12)
New Table = 'Table'
(3)Create measures.
SelectedDateValue = SELECTEDVALUE('Calendar'[Date])
Flag = IF(MAX('New Table'[Date])=[SelectedDateValue],1,0)
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-tangjie-msft thank you for the help!!
just a follow up,
Does this logic work if I create another field for Previous Category BK then put it next to the Current Category BK?
Since we already have one for the Current, i need to have another calendar slicer for the Previous. How do i do that?
SO IMAGINE 22/06/2023 is selected for the Previous Calendar Date,
this will be the expected output:
Opportunity | Project | Project Name | Current Category BK | Previous Category BK |
042452503602QVayAAG | PROJ-19191 | Zoora Bank- Support on Telco | RC_PS_TM | RC_CONSULTING_TIME_AND_MATERIALS |
Hi @cruzp ,
(1)Creates a second calendar table as a slicer to select the previous date.
Calendar2 = CALENDARAUTO(12)
(2)Create a measure.
Previous Category BK = CALCULATE(MAX('Table'[Category Bk]),FILTER(ALL('Table'),'Table'[Date]=SELECTEDVALUE('Calendar2'[Date])))
Best Regards,
Neeko Tang
Hi @v-tangjie-msft , how do i modify the DAX if the value is BLANK or the selected date does not have a category value, it will show "Null"?
The formula you provided is fine but it is showing empty/blank. I wanted to show "Null" if its empty/blank
Hi @cruzp ,
Try it.
Previous Category BK =
var _a= CALCULATE(MAX('Table'[Category Bk]),FILTER(ALL('Table'),'Table'[Date]=SELECTEDVALUE('Calendar2'[Date])))
return if(_a=BLANK(),"Null",_a)
Best Regards,
Neeko Tang
Hi,
In the Query Editor, use the "Fill Down" feature to fill in the blanks of the first 3 columns. Create a Calendar Table and a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table. Create a slicer from the Date column of the Calendar Table and select a Date. To a Table visual jst drag the 4 columns.
Hope this helps.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
123 | |
85 | |
85 | |
70 | |
51 |
User | Count |
---|---|
205 | |
157 | |
97 | |
79 | |
69 |