Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
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 |
---|---|
105 | |
99 | |
99 | |
38 | |
37 |
User | Count |
---|---|
157 | |
120 | |
74 | |
72 | |
63 |