The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
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 |
---|---|
37 | |
23 | |
23 | |
18 | |
16 |