Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I have been observing following. There are two date fields in main Table, which are connected with date table 'MainDate' and 'OtherDate'. Total rows in Table are 5, these 5 rows appear only when my both date slicers are cleared fully, if I disturb any one slicer by manually altering (even well beyound date range of that field in main Table) visible rows are reduced and they dont come back if I pull back slicer to end again. The only way to see all the rows is press clear selection button of the slicers.
I am unable to understand why it should happen this way and how to handle this situation.
Please find attached sample file:
https://drive.google.com/file/d/1DvyKgEDGcC_UkDUtKxk-Q75_B5Nd5HvC/view?usp=sharing
Thanks for your support.
Solved! Go to Solution.
Hi @mahenkj2 ,
In this case remove the relationships and use the following formula:
Filter Values =
VAR _DatesSelection = ALLSELECTED(MainDate[MainInvoiceDate])
RETURN
SUMX(
FILTER(
'Table',
('Table'[Main invoice] IN _DatesSelection && NOT (ISBLANK('Table'[Main invoice])))
||
('Table'[Other invoice] IN _DatesSelection && NOT (ISBLANK('Table'[Other invoice])))
),
1
)
Now use this has a filter in the visual:
I have added a line with both dates filled and you can see on the first image and on the last image the selections.
PBIX attached.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @mahenkj2 ,
The problem you are facing is based on context, since you are making the selection it get pulled in to the filter context for both the slicer and they are used as a IN statemetn meaning that it's looking for the values within the both dates at the same time.
Explaining a little bit better when you don't have any selection you do not have any filters (the initial status of your slicer) when you start to move the slicer then the values get picked up and when drag it to the end of the slicer that value is maintain in the filter this generates a query similar to this:
Has you can see the values on the slicer are persistent
When you clear the filter you get to the initial result that is:
Has you can see there is no filter. Since where you have values for other and for invoice there is blank value you get an incomplet result.
Do you have any case where the Main Invoice date and the Other invoice date are both filled?
If not you can combine those two on a single column and have a single slicer that will allow you to do the correct filter everytime, in the PBIX file I used the table MainInvoice date as slicer but is working properly.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Thanks for quick response.
I expected something similar, infact wanted to add more details of my actual scenario where both date column have situation when they have dates filled in and I can not merge them to connect with a universal date column. Did not indicate, just not to complicate much.
As a report developer, it looks no problem to me that I need to clear the slicer before making any inference of the report shown. Since this report is of finanicial purpose, user would not be comfortable in seeing the report based on date filters, and occassionaly clearing it the slicers to see if they are not missing any data.
Is there any other way, I can make a universal date column and then I will remove these two slicers, or use both slicer in some way, that selecting one slicers clears out another, if that makes sense in such scenarion?
Hi @mahenkj2 ,
In this case remove the relationships and use the following formula:
Filter Values =
VAR _DatesSelection = ALLSELECTED(MainDate[MainInvoiceDate])
RETURN
SUMX(
FILTER(
'Table',
('Table'[Main invoice] IN _DatesSelection && NOT (ISBLANK('Table'[Main invoice])))
||
('Table'[Other invoice] IN _DatesSelection && NOT (ISBLANK('Table'[Other invoice])))
),
1
)
Now use this has a filter in the visual:
I have added a line with both dates filled and you can see on the first image and on the last image the selections.
PBIX attached.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Thanks, the solution you suggest in your sample file is as desired, I implemented this in my actual work, the filter visual measure indicate about 1500 rows to return in the table visual, but it takes little time to load and show this error:
Upon reducing date slicer, I can see the table visual properly. I think even if I use all the date, just 1500 rows is not large.
Is it due to date table stored in the date variable in the measure we created, but I can not reduce that, in fact with time that may even increase few years?
Hi @MFelix ,
I found the reason, YYMM column from those date tables were yet the part of visual after removing existing table relationship.
Once I removed those fields from the visual, it is working as expected. Though, I would be fully satisfied if I could use date tables with relationships and not as disconnected tables and DAX measures. If there would be some ways to work with those slicers, it would be great.
Hi @mahenkj2 ,
Not sure why you need the related tables, probably because of some other visualizations but you can also try the following measure:
Filter Values =
VAR _MainSelection = ALLSELECTED(MainDate[MainInvoiceDate])
VAR _OtherSelection = ALLSELECTED(OtherDate[OtherInvoiceDate])
var _FinalTable =CALCULATETABLE('Table', REMOVEFILTERS(MainDate[MainInvoiceDate]), REMOVEFILTERS(OtherDate[OtherInvoiceDate]))
RETURN
SUMX(
FILTER(
_FinalTable,
('Table'[Main invoice] IN _MainSelection && NOT (ISBLANK('Table'[Main invoice])))
||
('Table'[Other invoice] IN _OtherSelection && NOT (ISBLANK('Table'[Other invoice])))
),
1
)
Check PBIX file attach, sorry for giving this alternative only now.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Appreciate. I think you suggest to use this alternate in place of earlier created measure. Whats potential advantage of this change? Is it to speed up mainly?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |