The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi, I've created a visual matrix in which I've the date as rows and some values for each columns. What I would like to do is to hide sundays and saturdays (in which most values are blank) from the visual, but If I select all the day names except them the values change because it is used in the column measures. Is there a way to only hide these days but keeping them in the calculations? thanks. Here's a screen of my report.
Solved! Go to Solution.
Hi @Hydrarian ,
Here I have created a sample for your reference, please check the following steps as below.
1. Create a calcualted column in date table.
Column = IF(WEEKDAY('Table'[Date],2)>5,0,1)
2. Then we can filter the visual by the calculated column like the picture as below.
For more detials, please check the pbix as attached.
Hi @Hydrarian ,
Here I have created a sample for your reference, please check the following steps as below.
1. Create a calcualted column in date table.
Column = IF(WEEKDAY('Table'[Date],2)>5,0,1)
2. Then we can filter the visual by the calculated column like the picture as below.
For more detials, please check the pbix as attached.
Hello @Hydrarian
only know I saw that you asked to keep the rows in, but without calculation
Then my solution won't work. You have to go for DAX and create a new measure like this
NewSum = SUMX('Table';if(WEEKDAY('Table'[Date];2)<5;SUM('Table'[Value]);0))
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Hi @Jimmy801 , I appreciateyour answer, but I want to hide the row but maintain the calculations and not viceversa. If I exclude sunday and saturday with a filter, the values in the other days change and I don't want that.
Hi @Hydrarian
Try something like below.
Measure =
VAR __values = SUM( 'Table'[Value] )
RETURN
IF(
ISINSCOPE( 'Calendar'[Date] ),
IF(
NOT( WEEKDAY( SELECTEDVALUE( 'Calendar'[Date] ), 2 ) IN { 6, 7 } ),
__values ),
__values
)
Hello @Hydrarian
then go for this solution. This should exclude the calculation of weekends, but in the subtotals should be considered
newsum1 = SWITCH(SELECTEDVALUE('Table'[Date]); BLANK(); SUM( [Value]);SUMX(FILTER('Table'; WEEKDAY('Table'[Date];2)<5);[Value]))
This datatable
should result in this
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Hello @Hydrarian
is this data passed somehow in Power Query?
Supposing the date is a column, you can filter the date-field for working days.
See an example, how this can be achieved
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTVMzTSMzIwtFTSUXJyDQKSBUX56UDKUClWByhvhlVeNze1OBXINoIoQjbEJ9QdYYgxpiEIeZghJpiGDJxLYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Type = _t, Spalte1 = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Type", type text}, {"Spalte1", type text}, {"Value", Int64.Type}}),
SelectWorkingDays = Table.SelectRows
(
#"Changed Type",
each Date.DayOfWeek(_[Date],Day.Monday)<5
)
in
SelectWorkingDays
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
34 | |
19 | |
18 | |
16 | |
13 |